Can you do a "Join" within a "LEFT OUTER JOIN"? My SQL is:

SELECT ol.plc_id + '|' + CAST(ol.orderlog_nbr AS varchar(12))
                                               AS orderlog_id,
       ol.seal_type,
       ol.bag_markings,
       ol.bag_tag                              AS tags,
       ol.charge_inspection_to_id,
       olbo.discharge_port_serial_nbr,
       olbo.loading_port_serial_nbr,
       ol.docs_sent_to_customer_date,
       CAST(NULL AS varchar(100))              AS instruction_note,
       ollid.export_document_nbr,
       ollid.carrier_bol,
       ollid.phyto_certificate,
       ollid.inspection_certificate,
       s1_country.export_declaration_nbr_label
  FROM s1_orderlog ol
       LEFT OUTER JOIN s1_orderlog_booking_order olbo
         ON ol.plc_id = olbo.plc_id
        AND ol.orderlog_nbr = olbo.order_nbr
       LEFT OUTER JOIN s1_orderlog_loading_instr_doc ollid
         ON ollid.orderlog_nbr = ol.orderlog_nbr
        AND ol.plc_id = ollid.plc_id
       LEFT OUTER JOIN s1_name_and_address
            JOIN s1_country
              ON s1_country.country_code = s1_name_and_address.country_code
         ON s1_name_and_address.name_and_address_id = ol.origin_id
;

It produces the following error: Could not execute statement. Syntax error near 'ON' on line 26 SQLCODE=-132, ODBC 3 State="42000"

Line 26 is: JOIN s1_country

asked 27 Dec '19, 13:43

murraysobol's gravatar image

murraysobol
793611
accept rate: 16%

edited 27 Dec '19, 14:01

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749

When I run it it says line 26 is this line...

ON s1_name_and_address.name_and_address_id = ol.origin_id

(27 Dec '19, 14:02) Breck Carter

I've never had much luck fiddling around with ( parentheses around join operators ).

You may want a derived table instead, which is a different way to use ( parentheses )...

SELECT ol.plc_id + '|' + CAST(ol.orderlog_nbr AS varchar(12))
                                               AS orderlog_id,
       ol.seal_type,
       ol.bag_markings,
       ol.bag_tag                              AS tags,
       ol.charge_inspection_to_id,
       olbo.discharge_port_serial_nbr,
       olbo.loading_port_serial_nbr,
       ol.docs_sent_to_customer_date,
       CAST(NULL AS varchar(100))              AS instruction_note,
       ollid.export_document_nbr,
       ollid.carrier_bol,
       ollid.phyto_certificate,
       ollid.inspection_certificate,
       s1_name_address_country.export_declaration_nbr_label
  FROM s1_orderlog ol
       LEFT OUTER JOIN s1_orderlog_booking_order olbo
         ON ol.plc_id = olbo.plc_id
        AND ol.orderlog_nbr = olbo.order_nbr
       LEFT OUTER JOIN s1_orderlog_loading_instr_doc ollid
         ON ollid.orderlog_nbr = ol.orderlog_nbr
        AND ol.plc_id = ollid.plc_id
       LEFT OUTER JOIN 
          ( SELECT s1_name_and_address.*,
                   s1_country.export_declaration_nbr_label 
              FROM s1_name_and_address
              JOIN s1_country
                ON s1_country.country_code
                 = s1_name_and_address.country_code
          ) AS s1_name_address_country
         ON s1_name_address_country.name_and_address_id = ol.origin_id
;

Caveat: I have NOT tested that.

(27 Dec '19, 14:11) Breck Carter

Breck: close...but no cigar!! When I execute this SQL: SELECT s1_name_and_address.*, s1_country.export_declaration_nbr_label FROM s1_name_and_address JOIN s1_country ON s1_country.country_code = s1_name_and_address.country_code it gives me the expected results.

But when I run the entire query it gives this error: Could not execute statement. Syntax error near 'SELECT' on line 24 SQLCODE=-131, ODBC 3 State="42000"

And. of course, line 24 is the inner query.

(27 Dec '19, 14:25) murraysobol
Replies hidden

Please show the exact query that gave the "Syntax error near 'SELECT' on line 24".

I get a semantic error (expected), not a syntax error...

Could not execute statement.
Table 's1_orderlog' not found
SQLCODE=-141, ODBC 3 State="42S02"
Line 1, column 1
(27 Dec '19, 16:14) Breck Carter

If you want to embed a join within another join, use parantheses around the "inner" join, such as...

from A left join
   (B inner join C on B.c1 = C.c1)
   on A.x = B.x

Without parantheses, joins are interpreted from left to right, so in your case the "JOIN" is not expected because the LEFT JOIN's ON condition is missing.

permanent link

answered 27 Dec '19, 13:51

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749
accept rate: 34%

edited 27 Dec '19, 13:55

Volker: I modified the SQL as suggested: LEFT OUTER JOIN s1_name_and_address (JOIN s1_country ON s1_country.country_code = s1_name_and_address.country_code ) ON s1_name_and_address.name_and_address_id = ol.origin_id

but it produces the same error.

FYI: Interactive SQL version 17.0.4, build 2053

(27 Dec '19, 14:08) murraysobol
Replies hidden

No, see my sample, the table has to be the first element within the parantheses, not the JOIN keyword, so the opening paranthesis must be before s1_name_and_address...

(27 Dec '19, 14:31) Volker Barth
1

Always show the exact code you are referring to... always... always... always... always... always... that's 5 "always", the maximum possible :)

(27 Dec '19, 16:18) Breck Carter

got it :)

(21 Jan, 16:12) murraysobol
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:

×22
×7

question asked: 27 Dec '19, 13:43

question was seen: 238 times

last updated: 21 Jan, 16:12