Link to home
Start Free TrialLog in
Avatar of dizmaRider
dizmaRider

asked on

Using results of Pivot clause in subquery

I need to use the results generated by a Pivot clause in a subquery.  However, I get an invalid identifier whenever I use the column alias generated by the Pivot clause.

I am using PL/SQL, Oracle 11g, and I need to know if this is even possible.  Below is the code I am using to test the concept.

WITH t_date_range AS
    (SELECT epic_util.efn_din (:StartDate) start_date, epic_util.efn_din (:EndDate) end_date, ROWNUM FROM DUAL),
   
Wounds as

                (
                    Select *    
                    from
                        (
                            Select /*+ no_index(flo_rows pk_ip_flwsht_meas) index(flo_rows) */
                                Flo_rows.flo_meas_id
                                , Flo_Rows.RECORDED_TIME
                                , Max(Flo_Rows.MEAS_VALUE) as Meas_Value
                                , UlcerPat.Pat_Enc_csn_id
                                , Max(UlcerPat.Pat_id) as Pat_id

                            From IP_FLWSHT_MEAS Flo_Rows
                                inner join IP_FLWSHT_REC Flo_pat on Flo_Rows.FSD_ID = Flo_pat.FSD_ID
                                    inner join
                                           -- all patients with wound flowsheet where wound type = Pressure Ulcer during specified date range
                                        (
                                            Select /*+ no_index(flo_rows pk_ip_flwsht_meas) index(flo_rows) */
                                                F_pat.inpatient_data_id
                                                , Enc.pat_id
                                                , Enc.pat_enc_csn_id

                                            From IP_FLWSHT_MEAS F_Rows
                                                inner join IP_FLWSHT_REC F_pat on F_Rows.FSD_ID = F_pat.FSD_ID
                                                    inner join PAT_ENC Enc on F_pat.inpatient_data_id = Enc.inpatient_data_id
                                                        inner join t_date_range on 1=1
                                                                   
                                            Where f_rows.flo_meas_id = '12248312' and f_rows.meas_value = 'Pressure Ulcer'
                                                and (f_rows.RECORDED_TIME >= t_date_range.start_date and f_rows.RECORDED_TIME < t_date_range.end_date+1)
                                        ) UlcerPat
                                    on Flo_pat.inpatient_data_id = UlcerPat.inpatient_data_id
                                           
                            Where Flo_Rows.flo_meas_id in ('12248295', '12248459', '12248312', '12248298', '12248314', '12248460','12248297')
                           
                            Group By UlcerPat.Pat_Enc_csn_id
                                , flo_rows.flo_meas_id
                                , Flo_Rows.Recorded_Time
                        ) Measures
                    PIVOT (max(meas_value)
                For flo_meas_id in ('12248295' as "Wound_Number",'12248297' as "Onset_Date", '12248298' as "Location", '12248312' as "Wound_Type", '12248314' as "Category",
                                    '12248459' as "Status", '12248460' as "Resolved_Date"))
                )
               
Select Wounds.Wound_Number
    from Wounds
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dizmaRider
dizmaRider

ASKER

Thanks for the quick response.  I thought I had tried every iteration of using the double quotes.  Thanks for helping me refocus.