I'm having the following SQL query which works well.
SELECT DISTINCT
tew_componentparent.com_tagpath AS vcomcom_com_tagpath
, tew_component.com_tagpath AS com_tagpath
, tew_component.com_id AS com_id
, tew_component.com_tagmanual AS com_tagmanual
, tew_componentelement.cel_group AS cel_group
, tew_componentelement.cel_orderno AS cel_orderno
, tew_componentelement.cel_id AS cel_id
, tew_componentterminal.cte_txt AS cte_txt
, tew_componentterminal.cte_no AS cte_no
, vew_wire_ex.cab_id AS cab_id
, vew_wire_ex.cab_tag AS cab_tag
, vew_wire_ex.caw_no AS caw_no
, vew_wire_ex.caw_section AS caw_section
, vew_wire_ex.caw_tra_0_l1 AS caw_tra_0_l1
, vew_wire_ex.wir_tag AS wir_tag
, vew_wire_ex.wir_id AS wir_id
, vew_wire_ex.vwircomto_com_id AS vwircomto_com_id
, vew_wire_ex.wir_type AS wir_type
, vew_wire_ex.equ_text AS equ_text
, vew_wire_ex.equ_id AS equ_id
, vew_wire_ex.equ_columnmark AS equ_columnmark
, vew_wire_ex.equ_rowmark AS equ_rowmark
, vew_wire_ex.cab_ductpath AS cab_ductpath
, vew_wire_ex.vwircomcomfrom_com_tagpath AS vwircomcomfrom_com_tagpath
, vew_wire_ex.vwircomcomto_com_tagpath AS vwircomcomto_com_tagpath
, vew_wire_ex.vwircomfrom_com_tagpath AS vwircomfrom_com_tagpath
, vew_wire_ex.vwircomto_com_tagpath AS vwircomto_com_tagpath
, vew_wire_ex.vwircomfrom_com_tagmanual AS vwircomfrom_com_tagmanual
, vew_wire_ex.vwircomto_com_tagmanual AS vwircomto_com_tagmanual
, vew_wire_ex.vwircelfrom_cel_group AS vwircelfrom_cel_group
, vew_wire_ex.vwircelto_cel_group AS vwircelto_cel_group
, vew_wire_ex.vwirctefrom_cte_txt AS vwirctefrom_cte_txt
, vew_wire_ex.vwircteto_cte_txt AS vwircteto_cte_txt
, locfrom.loc_tagpath AS locfrom_tagpath
, locto.loc_tagpath AS locto_tagpath
, funfrom.fun_tagpath AS funfrom_tagpath
, funto.fun_tagpath AS funto_tagpath
, funfrom.fun_tra_0_l1 AS funfrom_tra_0_l1
, funto.fun_tra_0_l1 AS funto_tra_0_l1
, locfrom.loc_tra_0_l1 AS locfrom_tra_0_l1
, locto.loc_tra_0_l1 AS locto_tra_0_l1
, locto.loc_id AS loc_to_id
, CASE WHEN vwircomto_com_id = tew_component.com_id THEN vwircomcomfrom_com_tagpath ELSE vwircomcomto_com_tagpath END AS comdest_comcom_tagpath
, CASE WHEN vwircomto_com_id = tew_component.com_id THEN vwircomfrom_com_tagpath ELSE vwircomto_com_tagpath END AS comdest_com_tagpath
, CASE WHEN vwircomto_com_id = tew_component.com_id THEN vwircomfrom_com_tagmanual ELSE vwircomto_com_tagmanual END AS comdest_com_tagmanual
, CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN vwircelfrom_cel_group ELSE vwircelto_cel_group END AS comdest_cel_group
, CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN vwirctefrom_cte_txt ELSE vwircteto_cte_txt END AS comdest_cte_txt
, CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN locfrom.loc_tagpath ELSE locto.loc_tagpath END AS comdest_loc_tagpath
, CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN funfrom.fun_tagpath ELSE funto.fun_tagpath END AS comdest_fun_tagpath
, CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN funfrom.fun_tra_0_l1 ELSE funto.fun_tra_0_l1 END AS comdest_fun_tra_0_l1
, CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN locfrom.loc_tra_0_l1 ELSE locto.loc_tra_0_l1 END AS comdest_loc_tra_0_l1
, vew_wire_ex.cab_standard AS cab_standard
, vew_wire_ex.wir_sectionorgauge AS wir_sectionorgauge
, vew_wire_ex.wir_sectiontype AS wir_sectiontype
, vew_plcinout_ex.cel_id AS GtW_CELID
, vew_plcinout_ex.aio_tra_0_l1
, vew_plcinout_ex.aio_tra_1_l1
, vew_plcinout_ex.aio_tra_2_l1
, vew_plcinout_ex.aio_tra_3_l1
, vew_plcinout_ex.aio_tra_4_l1
, vew_plcinout_ex.aio_mnemonic
, vew_plcinout_ex.aio_id
, vew_plcinout_ex.aio_cel_id
, vew_plcinout_ex.bom_reference AS bom_reference
FROM vew_plcinout_ex
INNER JOIN
tew_componentelement ON vew_plcinout_ex.cel_id = tew_componentelement.cel_id LEFT OUTER JOIN
vew_wire_ex LEFT OUTER JOIN
tew_componentterminal ON vew_wire_ex.wir_cel_idfrom = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_nofrom = tew_componentterminal.cte_no OR
vew_wire_ex.wir_cel_idto = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_noto = tew_componentterminal.cte_no ON tew_componentelement.cel_id = tew_componentterminal.cte_cel_id LEFT OUTER JOIN
tew_component ON tew_component.com_id = tew_componentelement.cel_com_id LEFT OUTER JOIN
tew_component AS tew_componentparent ON tew_componentparent.com_id = tew_component.com_com_id LEFT OUTER JOIN
vew_location AS locfrom ON vew_wire_ex.vwircomfrom_com_loc_id = locfrom.loc_id LEFT OUTER JOIN
vew_location AS locto ON vew_wire_ex.vwircomto_com_loc_id = locto.loc_id LEFT OUTER JOIN
vew_function AS funfrom ON vew_wire_ex.vwircomfrom_com_fun_id = funfrom.fun_id LEFT OUTER JOIN
vew_function AS funto ON vew_wire_ex.vwircomto_com_fun_id = funto.fun_id
WHERE (vew_plcinout_ex.aio_cel_id > - 1)
But in SQLite it gives an error on "ON".
It seems that this string is causing the issue:
LEFT OUTER JOIN tew_componentterminal ON
vew_wire_ex.wir_cel_idfrom = tew_componentterminal.cte_cel_id AND
vew_wire_ex.wir_cte_nofrom = tew_componentterminal.cte_no OR
vew_wire_ex.wir_cel_idto = tew_componentterminal.cte_cel_id AND
vew_wire_ex.wir_cte_noto = tew_componentterminal.cte_no
ON tew_componentelement.cel_id = tew_componentterminal.cte_cel_id
But how can I reformat it that SQLite it understands.
Hard to test it out locally, but playing around with brackets at least solved the syntax issue.
INNER JOIN tew_componentelement ON vew_plcinout_ex.cel_id = tew_componentelement.cel_id
LEFT OUTER JOIN vew_wire_ex ON tew_componentelement.cel_id = vew_wire_ex.vwircelto_cel_id OR tew_componentelement.cel_id = vew_wire_ex.vwircelfrom_cel_id
LEFT OUTER JOIN tew_componentterminal ON
(
(vew_wire_ex.wir_cel_idfrom = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_nofrom = tew_componentterminal.cte_no)
OR
(vew_wire_ex.wir_cel_idto = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_noto = tew_componentterminal.cte_no)
) AND tew_componentelement.cel_id = tew_componentterminal.cte_cel_id