?
Solved

Using results of Pivot clause in subquery

Posted on 2015-01-08
2
Medium Priority
?
450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

777 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