Search code examples
mysqlsqlodbc

Change {oj } SQL query into a normal SQL syntax


Can I ask for your assistance to help me convert this {oj } SQL syntax into the normal join syntax?

SELECT
...
FROM
 {oj (((((((((("VMPH" "VMPH" LEFT OUTER JOIN "VMPT" "VMPT" ON "VMPH"."NMPSTID"="VMPT"."NMPSTID")
 LEFT OUTER JOIN "ARRTA" "ARRTA" ON "VMPH"."TXTERM"="ARRTA"."CODETERM")
 LEFT OUTER JOIN "VMAGRE" "VMAGRE" ON "VMPH"."NMAGRE"="VMAGRE"."NMDOCID")
 LEFT OUTER JOIN "VMDH" "VMDH" ON "VMPH"."NMDOCID"="VMDH"."NMDOCID")
 LEFT OUTER JOIN "VMPTIT" "VMPTIT" ON ("VMPT"."NMPSTID"="VMPTIT"."NMPSTID") AND ("VMPT"."WDTRANNUM"="VMPTIT"."WDTRANNUM"))
 LEFT OUTER JOIN "VMPTSV" "VMPTSV" ON ("VMPT"."NMPSTID"="VMPTSV"."NMPSTID") AND ("VMPT"."WDTRANNUM"="VMPTSV"."WDTRANNUM"))
 LEFT OUTER JOIN "VMPTEQ" "VMPTEQ" ON ("VMPT"."NMPSTID"="VMPTEQ"."NMPSTID") AND ("VMPT"."WDTRANNUM"="VMPTEQ"."WDTRANNUM"))
 LEFT OUTER JOIN "VMDTEQ" "VMDTEQ" ON ("VMPT"."NMDOCID"="VMDTEQ"."NMDOCID") AND ("VMPT"."WDDTTRNUM"="VMDTEQ"."WDTRANNUM"))
 LEFT OUTER JOIN "VMMTRM" "VMMTRM" ON ("VMPTEQ"."TXEQUP"="VMMTRM"."TXEQUP") AND ("VMPTEQ"."TXMETER"="VMMTRM"."TXMETER"))
 LEFT OUTER JOIN "VMEQUP" "VMEQUP" ON ("VMPTEQ"."TXEQUP"="VMEQUP"."TXEQUP"))
 LEFT OUTER JOIN "VMSERV" "VMSERV" ON ("VMPTSV"."WDSERVTYPE"="VMSERV"."WDTYPE") AND ("VMPTSV"."TXSERV"="VMSERV"."TXSERV")}

I tried removing the brackets and "oj", but it's not working. I tried this one below, but not working as well

SELECT
...
WHERE
"VMPH"."NMPSTID" = "VMPT"."NMPSTID"
 AND "VMPH"."TXTERM" = "ARRTA"."CODETERM"
 AND "VMPH"."NMAGRE" = "VMAGRE"."NMDOCID"
 AND "VMPH"."NMDOCID" = "VMDH"."NMDOCID"
 AND (("VMPT"."NMPSTID" = "VMPTIT"."NMPSTID") AND ("VMPT"."WDTRANNUM" = "VMPTIT"."WDTRANNUM"))
 AND (("VMPT"."NMPSTID" = "VMPTSV"."NMPSTID") AND ("VMPT"."WDTRANNUM" = "VMPTSV"."WDTRANNUM"))
 AND (("VMPT"."NMPSTID"="VMPTEQ"."NMPSTID") AND ("VMPT"."WDTRANNUM"="VMPTEQ"."WDTRANNUM"))
 AND (("VMPT"."NMDOCID"="VMDTEQ"."NMDOCID") AND ("VMPT"."WDDTTRNUM"="VMDTEQ"."WDTRANNUM"))
 AND (("VMPTEQ"."TXEQUP"="VMMTRM"."TXEQUP") AND ("VMPTEQ"."TXMETER"="VMMTRM"."TXMETER"))
 AND "VMPTEQ"."TXEQUP" = "VMEQUP"."TXEQUP"
 AND (("VMPTSV"."WDSERVTYPE"="VMSERV"."WDTYPE") AND ("VMPTSV"."TXSERV"="VMSERV"."TXSERV"))

Solution

  • In mysql identifiers (ie table names,column names) must be enclosed in backticks if they are reserved words, none of yours are so you can lose the double quotes. Also oj (whatever that is) is assigning aliases which aren't particularly helpful so I would lose those and the brackets. eg

    SELECT *
    FROM  VMPH  
     LEFT OUTER JOIN VMPT  ON VMPH.NMPSTID=VMPT.NMPSTID
     LEFT OUTER JOIN ARRTA ON VMPH.TXTERM=ARRTA.CODETERM
     LEFT OUTER JOIN VMAGRE ON VMPH.NMAGRE=VMAGRE.NMDOCID
     LEFT OUTER JOIN VMDH  ON VMPH.NMDOCID=VMDH.NMDOCID
     LEFT OUTER JOIN VMPTIT ON VMPT.NMPSTID=VMPTIT.NMPSTID AND VMPT.WDTRANNUM=VMPTIT.WDTRANNUM
     LEFT OUTER JOIN VMPTSV ON VMPT.NMPSTID=VMPTSV.NMPSTID AND VMPT.WDTRANNUM=VMPTSV.WDTRANNUM
     LEFT OUTER JOIN VMPTEQ ON VMPT.NMPSTID=VMPTEQ.NMPSTID AND VMPT.WDTRANNUM=VMPTEQ.WDTRANNUM
     LEFT OUTER JOIN VMDTEQ ON VMPT.NMDOCID=VMDTEQ.NMDOCID AND VMPT.WDDTTRNUM=VMDTEQ.WDTRANNUM
     LEFT OUTER JOIN VMMTRM ON VMPTEQ.TXEQUP=VMMTRM.TXEQUP AND VMPTEQ.TXMETER=VMMTRM.TXMETER
     LEFT OUTER JOIN VMEQUP ON VMPTEQ.TXEQUP=VMEQUP.TXEQUP
     LEFT OUTER JOIN VMSERV ON VMPTSV.WDSERVTYPE=VMSERV.WDTYPE AND VMPTSV.TXSERV=VMSERV.TXSERV;
    

    Still go issues ? add all table definitions.