|
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 |
|
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. 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 '20, 16:12)
murraysobol
|
When I run it it says line 26 is this line...
ON s1_name_and_address.name_and_address_id = ol.origin_id
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.
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.
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...