sqlsqlite

Modify SQL query to SQLite syntax


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.


Solution

  • 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