Solved

Using results of Pivot clause in subquery

Posted on 2015-01-08
2
379 Views
Last Modified: 2015-01-08
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
0
Comment
Question by:dizmaRider
2 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40538448
"Wound_Number"

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

Select Wounds."Wound_Number"
    from Wounds

or, remove the double quotes
0
 

Author Closing Comment

by:dizmaRider
ID: 40538489
Thanks for the quick response.  I thought I had tried every iteration of using the double quotes.  Thanks for helping me refocus.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now