HCPosh missing-alias finder incorrectly flagging UNPIVOT columns

[davidshimamoto]

The "missing alias" finder in HCPosh is identifying some columns that cannot be prefixed. An example is when doing an UNPIVOT, the columns that you specify in the unpivot function cannot be prefixed.

I attempted to prefix them and got this SQL Server error:
Msg 275, Level 16, State 1, Line 61
Prefixes are not allowed in value or pivot columns of an UNPIVOT operator.

[davidshimamoto]

SELECT CAST(pe.PatientEncounterID AS VARCHAR(250)) + '_' + CAST(oc.ID AS VARCHAR(4)) AS ObservationID ,CAST('Epic' AS VARCHAR(255)) AS ObservationSourceDSC ,CAST('Encounter' AS VARCHAR(255)) AS SourceContextDSC ,CAST(pe.PatientEncounterID AS VARCHAR(255)) AS EncounterID ,CAST('Epic' AS VARCHAR(255)) AS EncounterSourceDSC ,CAST(pe.PatientID AS VARCHAR(255)) AS PatientID ,CAST(oc.ID AS VARCHAR(255)) AS ObservationCD ,CAST(pe.ObservationDSC AS VARCHAR(255)) AS ObservationDSC ,CAST(oc.CodeSystemDSC AS VARCHAR(255)) AS CodeSystemDSC ,CAST(oc.ObservationNormCD AS VARCHAR(255)) AS ObservationNormCD ,CAST(oc.ObservationNormDSC AS VARCHAR(255)) AS ObservationNormDSC ,CAST(COALESCE(pe.HospitalAdmitDTS,pe.EncounterDTS,pe.ContactDTS) AS DATETIME2) AS EffectiveDTS ,CAST(pe.EntryTimeDTS AS DATETIME2) AS RecordedDTS ,CAST(NULL AS VARCHAR(255)) AS RecordingProviderID ,CAST(pe.ValueTXT AS VARCHAR(255)) AS ValueTXT ,TRY_CAST(pe.ValueTXT AS NUMERIC(38,2)) AS ValueNBR ,CAST(CASE pe.ObservationDSC WHEN 'Blood Pressure Diastolic' THEN 'mmHg' WHEN 'Blood Pressure Systolic' THEN 'mmHg' WHEN 'Temperature' THEN 'f' WHEN 'Heart Rate' THEN 'bpm' WHEN 'Weight' THEN 'oz' WHEN 'Height' THEN 'ft and in' WHEN 'Respiration Rate' THEN 'bpm' WHEN 'Head Circumfrence' THEN 'Cm' END AS VARCHAR(255)) AS UnitDSC ,CAST('C' AS VARCHAR(255)) AS StatusCD ,CAST('Completed' AS VARCHAR(255)) AS StatusDSC ,CAST(pe.PatientEncounterID AS VARCHAR(255)) AS ParentID ,CAST(pe.EDWLastModifiedDTS AS DATETIME2) AS EDWLastModifiedDTS FROM ( SELECT PatientEncounterID ,PatientID ,EDWLastModifiedDTS ,EntryTimeDTS ,ContactDTS ,EncounterDTS ,HospitalAdmitDTS ,HospitalDischargeDTS ,CAST(BloodPressureDiastolicNBR AS VARCHAR(255)) AS [Blood Pressure Diastolic] ,CAST(BloodPressureSystolicNBR AS VARCHAR(255)) AS [Blood Pressure Systolic] ,CAST(TemperatureFahrenheitNBR AS VARCHAR(255)) AS [Temperature] ,CAST(HeartRateNBR AS VARCHAR(255)) AS [Heart Rate] ,CAST(WeightPoundsNBR AS VARCHAR(255)) AS [Weight] ,CAST(HeightTXT AS VARCHAR(255)) AS [Height] ,CAST(RespirationRateNBR AS VARCHAR(255)) AS [Respiration Rate] ,CAST(HeadCircumferenceCentimetersNBR AS VARCHAR(255)) AS [Head Circumference] FROM Epic.Encounter.PatientEncounter WHERE BloodPressureDiastolicNBR IS NOT NULL OR BloodPressureSystolicNBR IS NOT NULL OR TemperatureFahrenheitNBR IS NOT NULL OR HeartRateNBR IS NOT NULL OR WeightPoundsNBR IS NOT NULL OR HeightTXT IS NOT NULL OR RespirationRateNBR IS NOT NULL OR HeadCircumferenceCentimetersNBR IS NOT NULL ) AS up UNPIVOT(ValueTXT FOR ObservationDSC IN( [Blood Pressure Diastolic] ,[Blood Pressure Systolic] ,[Temperature] ,[Heart Rate] ,[Weight] ,[Height] ,[Respiration Rate] ,[Head Circumference])) AS pe INNER JOIN Shared.ClinicalConfig.ObservationConfiguration AS oc ON oc.SourceCD = pe.ObservationDSC AND oc.SourceSystemDSC = 'Epic' AND oc.SourceCategoryDSC = 'Encounter' AND oc.ActiveFLG = 1