oracle grammar to h2 grammar (+) join table

I have the following query as Oracle

 SELECT DISTINCT count(pa.payment_id) FROM location c, inventory e, inventory_stock es, payment_client ep, payment pa, currency cur, location s, exchange_country exc, exchange_rate sso, exchange_hike so, exchange_margin sov WHERE cur.outState = 'N' AND c.location_id = e.location_id AND e.inventory_id = ep.inventory_id AND e.inventory_stock_id = es.inventory_stock_id AND ep.client_id = pa.end_client AND pa.cur_id = cur.cur_id AND cur.location_id = s.location_id AND c.client_id is not null AND cur.cur_id = exc.cur_id(+) AND exc.exchange_id = sso.exchange_id(+) AND sso.account_id = so.account_id(+) AND so.option_name(+) = 'PREMIUM' AND exc.exchange_id = sov.exchange_id(+) AND sov.name(+) = 'VALUE'; 

Right now I am using H2 database and the syntax error I got was from so.option_name(+) and sov.name(+); I know the (+) are oracle's way of right join and left join but are there any possible way to convert this into h2 so the error and the grammar are equivalent?

1 Answer

It's time to move on. Oracle's legacy outer join syntax is no longer recommended by Oracle. From the docs:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax [...]

If you replace (+) usage by outer join, not only will your query work on both Oracle and H2, it will also be an important step forward for your application as a whole.

SELECT DISTINCT count(pa.payment_id) FROM location c JOIN inventory e ON c.location_id = e.location_id JOIN payment_client ep ON e.inventory_id = ep.inventory_id JOIN inventory_stock es ON e.inventory_stock_id = es.inventory_stock_id JOIN payment pa ON ep.client_id = pa.end_client JOIN currency cur ON pa.cur_id = cur.cur_id JOIN location s ON cur.location_id = s.location_id LEFT JOIN exchange_country exc ON cur.cur_id = exc.cur_id LEFT JOIN exchange_rate sso ON exc.exchange_id = sso.exchange_id LEFT JOIN exchange_hike so ON sso.account_id = so.account_id AND so.option_name = 'PREMIUM' LEFT JOIN exchange_margin sov ON exc.exchange_id = sov.exchange_id AND sov.name = 'VALUE' WHERE c.client_id IS NOT NULL AND cur.outState = 'N' 

The importance when converting from (+) to LEFT JOIN is that you pay close attention which predicates must go into an ON clause, and which predicates are fine in the WHERE clause. In particular, the following two predicates must go in the relevant left joined table's ON clause:

• so.option_name(+) = 'PREMIUM'
• sov.name(+) = 'VALUE'

Third party tooling

You can use jOOQ's online SQL translator to translate between the syntaxes, or use jOOQ directly to translate from table lists with Oracle joins to ansi joins.

Disclaimer: I work for the company behind jOOQ