Keeping it DRYer
Keith, March 22, 2017 - 1:10 pm UTC
I was converting SQL Server code where they're using cross apply.
Since I was using Oracle 11 I couldn't use outer apply and found that cross join table cast multiset did basically the same thing I was after. It was easier for me to define the table type and inline the query. I was able to work around this one with putting the conditions directly in the select portion but reviewing some of the other code it looks like I'll have to build functions to return tables and see where that gets me. The database I'm working with is using a lot of CROSS APPLY (SELECT TOP 1 ORDER BY key DESC) lookup because they want the last record. Unfortunately there would be a lot of DDL and data examples beyond what I can put together for a more valid reason but I appreciate the offer.
tip
Ghassan, March 22, 2017 - 4:33 pm UTC
using the inline hint will work thus;
WITH cte AS (
SELECT /*+ inline */ *
FROM vwTblCstMset
)
SELECT * FROM cte
UNION ALL SELECT * FROM cte
;
DUMMY FLD
----- ---
X Y
X Y
Any follow up?
A reader, April 10, 2017 - 2:27 pm UTC
April 11, 2017 - 12:36 am UTC
Sorry - not sure what you're after.