database - oracle文法转h2文法(+)join table

Question

我作为 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';

现在我正在使用 H2 数据库，我得到的语法错误来自 so.option_name(+) 和 sov.name(+); 我知道 ( +) 是 oracle 的右连接和左连接方式，但是否有任何可能的方法将其转换为 h2，以便错误和语法等效？

Answer 1

是时候继续前进了。 Oracle 不再推荐 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 [...]

如果您将 (+) 用法替换为 outer join，您的查询不仅可以在 Oracle 和 H2 上工作，而且对于您的查询来说也是重要的一步整个应用程序。

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'

从 (+) 转换为 LEFT JOIN 的重要性在于您要密切注意哪些谓词必须进入 ON 子句，哪些谓词在 WHERE 子句中是合适的。特别是，以下两个谓词必须进入相关的左连接表的 ON 子句:

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

第三方工具

你可以在线使用jOOQ SQL translator在语法之间进行转换，或者直接使用 jOOQ 到 translate from table lists with Oracle joins to ansi joins .

免责声明:我为 jOOQ 背后的公司工作