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

asked 08 Feb, 15:19

Murray%20Sobol's gravatar image

Murray Sobol
61227
accept rate: 0%

edited 08 Feb, 15:23

Mark%20Culp's gravatar image

Mark Culp
23.6k9134278


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

permanent link

answered 08 Feb, 15:26

Mark%20Culp's gravatar image

Mark Culp
23.6k9134278
accept rate: 40%

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, 15:41) Murray Sobol
Replies hidden

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.

You can put your code snippets between a pair of <pre> tags to have it displayed that way...

(09 Feb, 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)
    ...
permanent link

answered 09 Feb, 04:22

Volker%20Barth's gravatar image

Volker Barth
32.0k326468687
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×29

question asked: 08 Feb, 15:19

question was seen: 187 times

last updated: 09 Feb, 04:22