Query Builder converts ANSI SQL to Oracle SQL incorrectly, when sending original sql that has an outer join.
Steps to replicate:
1. opened the sql statement in Editor (see 1)
2. right-click -> Send to Query Builder
3. SQL statement in Query Builder has already removed the "left outer" from the query even before I convert from ANSI to Oracle SQL (see 2)
1. opened the sql statement in Query Builder (see 1)
2. SQL statement in Query Builder has already removed the "left outer" from the query even before I convert from ANSI to Oracle SQL (see 2)
So the actual issue is caused by simply opening the SQL statement in Query Builder.
1 Query before opening in Query Builder
select a.request_history_id
from f_request_history a left
outer join f_request_info_history b on a.source_request_id = b.source_request_id
and a.account_id = b.account_id
inner join d_account_109 da on a.account_id=da.account_id
inner join v_rmicw_collector_action vca on a.action_id = vca.action_id
where a.created_time >
(select
case when max(c.created_time) is null then 2999
else max(c.created_time)
end
from f_request_requestinfo c);
2 Query after opening in Query Builder
SELECT a.REQUEST_HISTORY_ID
FROM F_REQUEST_HISTORY a
JOIN F_REQUEST_INFO_HISTORY b
ON (a.SOURCE_REQUEST_ID = b.SOURCE_REQUEST_ID)
AND (a.ACCOUNT_ID = b.ACCOUNT_ID)
JOIN D_ACCOUNT_109 da
ON (a.ACCOUNT_ID = da.ACCOUNT_ID)
JOIN V_RMICW_COLLECTOR_ACTION vca
ON (a.ACTION_ID = vca.ACTION_ID)
WHERE (a.CREATED_TIME >
(SELECT CASE
WHEN MAX (c.created_time) THEN 2999
ELSE c.created_time
