July 18, 2012 at 4:27 pm
Hi all,
I am trying to figure out an alternative to nested with.
an example query is:
WITH VIR_TABLE1
AS (SELECT CASE T1.SUMMARY
WHEN 'XYZ' THEN
'XYZ123'
END AS SUMMARY,
SUBSTR(T1.DETAIL, 48, 9) AS IDNUM
FROM TABLE1 T1
WHERE T1.JOB_ID = 'IONR'
AND T1.DATEFIELD = '08-JUN-2012')
SELECT MAX(VT1.SUMMARY),
VT1.IDNUM,
COUNT(*) AS TOTAL_COUNT
FROM VIR_TABLE1 VT1
GROUP BY VT1.IDNUM
I want to inner join the results of this query with another table on idnum. I was trying to have another WITH clause on top of the query above and then doing a simple join but unfortunately after wasting some time i found out nested WITH is not yet supported.
If i dint want a count from the above query it would have been pretty straight forward but that count is important to be displayed in the final result.
Below is what i am trying to achieve (if it helps, it doesnt work though):
WITH VIR_TABLE2
AS (WITH VIR_TABLE1
AS (SELECT CASE T1.SUMMARY
WHEN 'XYZ' THEN
'XYZ123'
END AS SUMMARY,
SUBSTR(T1.DETAIL, 48, 9) AS IDNUM
FROM TABLE1 T1
WHERE T1.JOB_ID = 'IONR'
AND T1.DATEFIELD = '08-JUN-2012')
SELECT MAX(VT1.SUMMARY),
VT1.IDNUM,
COUNT(*) AS TOTAL_COUNT
FROM VIR_TABLE1 VT1
GROUP BY VT1.IDNUM)
SELECT VT2.*, PRV.PROVIDER_NUM
FROM VIR_TABLE2 VT2
INNER JOIN PROVIDER PRV ON VT2.IDNUM = PRV.IDNUM
Any help or ideas are appreciated (or the exact solution!).
TIA.
July 19, 2012 at 7:26 am
Cool, I dint know we could use them sequentially, this opens up endless possibilities.
Thanks a lot for the perfect reply.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply