|
I have a view that contains a "left outer join" within a "join" in the "from" clause in a view; here is the SQL: JOIN s1_currency_lang
ON s1_contract.currency_code = s1_currency_lang.currency_code
JOIN s1_release
LEFT OUTER JOIN gsr_release_load_balance
ON s1_release.contract_nbr = gsr_release_load_balance.contract_nbr
AND s1_release.release_nbr = gsr_release_load_balance.release_nbr
ON s1_contract.contract_nbr = s1_release.contract_nbr
How do I delimit the inner "left outer Join" from the outer "join" The error I get is: Could not execute statement. Syntax error near 'ON' on line 230 Line 230 is this line: ON s1_contract.contract_nbr = s1_release.contract_nbr I tried wrapping the "left Outer Join" with parentheses but that did not work. Thanks |
|
The ON clause between s1_contract and s1_release needs to be before the LEFT OUTER JOIN clause. E.g. JOIN s1_currency_lang
ON s1_contract.currency_code = s1_currency_lang.currency_code
JOIN s1_release
ON s1_contract.contract_nbr = s1_release.contract_nbr
LEFT OUTER JOIN gsr_release_load_balance
ON s1_release.contract_nbr = gsr_release_load_balance.contract_nbr
AND s1_release.release_nbr = gsr_release_load_balance.release_nbr
Mark: I see what you're saying but the results of the "join" on s1_release" table are meant to be filtered by the "left outer join" clause; I don't think your solution will produce the correct results. I understand that your solution will make the view compile. BTW, how did you get my initial post to format as I had entered it? When I saw the initial post LFs and CRs were eliminated; I wanted them to be there for reradibility purposes.
(08 Feb '18, 15:41)
Murray Sobol
Replies hidden
You can put your code snippets between a pair of <pre> tags to have it displayed that way...
(09 Feb '18, 04:10)
Volker Barth
|
|
In addition to Mark's answer, you can also use parantheses to specify the JOIN order and/or to group joins, such as: FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON B.ID = C.ID FROM A INNER JOIN (B INNER JOIN C ON B.ID = C.ID) ON A.ID = B.ID FROM A INNER JOIN (B LEFT JOIN C ON B.ID = C.ID) ON A.ID = B.ID Note, for inner joins the join order does not make a difference for the result as inner joins are commutative and associative, for left outer joins it might make a difference. I guess in your case parantheses around the following expression should work: ...
(s1_release
LEFT OUTER JOIN gsr_release_load_balance
ON s1_release.contract_nbr = gsr_release_load_balance.contract_nbr
AND s1_release.release_nbr = gsr_release_load_balance.release_nbr)
...
|
|
Volker: this solution: (s1_release LEFT OUTER JOIN gsr_release_load_balance ON s1_release.contract_nbr = gsr_release_load_balance.contract_nbr AND s1_release.release_nbr = gsr_release_load_balance.release_nbr) ... did not work - it produced the same error. Murray |
