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 *    
                            Select /*+ no_index(flo_rows pk_ip_flwsht_meas) index(flo_rows) */
                                , 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) */
                                                , 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
Who is Participating?
sdstuberConnect With a Mentor Commented:

is case sensitive, as are the others because of the double quotes

Select Wounds."Wound_Number"
    from Wounds

or, remove the double quotes
dizmaRiderAuthor Commented:
Thanks for the quick response.  I thought I had tried every iteration of using the double quotes.  Thanks for helping me refocus.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.