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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER