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"))
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.