Solved

Using results of Pivot clause in subquery

Posted on 2015-01-08
2
416 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 74

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

827 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