1

so my company is moving from Postgres to Oracle and I am moving a query I have in an application over that uses CROSS JOIN LATERAL to combine fields and create a terms list for a auto complete. Below is what I used for Postgres

SELECT 
DISTINCT terms as "catterms", tsource as "termsource"
FROM prodcats pc
INNER JOIN prodgenres pg on pc.productid = pg.productid
INNER JOIN genres g on pg.genreid = g.genreid
INNER JOIN products pn on pc.productid = pn.productid
INNER JOIN catnew cn on pn.modelcat = cn.categoryid
CROSS JOIN LATERAL ON (VALUES (cn.name, 'mc'), (g.name, 'pg'),
                    (pc.productsku, 'ps')) as t(terms, tsource) 
WHERE pc.categoryid = 42 ORDER BY termsource ASC, catterms ASC

I have tried doing my own attempts at Googling and could only come across LATERALs uses in Oracle and unpivot which I have gotten to combine columns, but they don't line up with the other values like my old query used. Running this query in Oracle gives me "Missing IN or OUT parameter at index:: 1" Any help you can provide would be appreciated.

dgrambcode
  • 11
  • 2
  • Oracle supports `cross join lateral` as well, it just doesn't support the `values()` clause like that. –  May 28 '21 at 17:05
  • @a_horse_with_no_name Yeah I knew lateral existed and cross join so I assumed cross join lateral existed, but finding an example to find syntax still has been a challenge. I appreciate your answer though. The solution was so easy to find for PG, but I have toyed with UNION and UNPIVOT for a couple hours just to end with results that just don't make sense. – dgrambcode May 28 '21 at 20:37

0 Answers0