The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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
61347
accept rate: 0%

edited 08 Feb, 15:23

Mark%20Culp's gravatar image

Mark Culp
23.8k9134279


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.8k9134279
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.4k328476692
accept rate: 32%

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

permanent link

answered 18 Jun, 16:48

murraysobol's gravatar image

murraysobol
11112
accept rate: 0%

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:

×33

question asked: 08 Feb, 15:19

question was seen: 405 times

last updated: 18 Jun, 16:48