My code is returning an error :
Here is my code :
SELECT "OINV"."DocNum" [Transaction Number], "OINV"."DocDate", "OINV"."CardName", "INV1"."Dscription", "OINV"."CardCode", "OINV"."BPLName", "INV1"."LineTotal", "INV1"."GTotal", "OACT"."AcctCode", "OACT"."AcctName", "OINV"."LicTradNum", "OINV"."Pindicator", "INV1"."Itemcode" FROM ("Database"."dbo"."OACT" "OACT" INNER JOIN "Database"."dbo"."INV1" "INV1" ON "OACT"."AcctCode"="INV1"."AcctCode") INNER JOIN "Database"."dbo"."OINV" "OINV" ON "INV1"."DocEntry"="OINV"."DocEntry"
UNION
SELECT "OCSI"."DocNum"[Transaction Number], "OCSI"."DocDate", "OCSI"."CardName", "CSI1"."Dscription", "OCSI"."CardCode", "OCSI"."BPLName", "CSI1"."LineTotal", "CSI1"."GTotal", "OACT"."AcctCode", "OACT"."AcctName", "OCSI"."LicTradNum", "OCSI"."Pindicator", "CSI1"."Itemcode" FROM ("Database"."dbo"."OCSI" "OCSI" INNER JOIN "Database"."dbo"."CSI1" "CSI1" ON "OCSI"."DocEntry"="CSI1"."DocEntry") INNER JOIN "Database"."dbo"."OACT" "OACT" ON "CSI1"."AcctCode"="OACT"."AcctCode"
UNION
SELECT "ORIN"."DocNum"[Transaction Number], "ORIN"."DocDate", "ORIN"."CardName", "RIN1"."Dscription", "ORIN"."CardCode", "ORIN"."BPLName", "RIN1"."LineTotal"*-1, "RIN1"."GTotal", "OACT"."AcctCode", "OACT"."AcctName", "ORIN"."LicTradNum", "ORIN"."Pindicator", "RIN1"."Itemcode" FROM ("Database"."dbo"."ORIN" "ORIN" INNER JOIN "Database"."dbo"."RIN1" "RIN1" ON "ORIN"."DocEntry"="RIN1"."DocEntry") INNER JOIN "Database"."dbo"."OACT" "OACT" ON "RIN1"."AcctCode"="OACT"."AcctCode"
UNION ALL
SELECT DISTINCT "_PM_BGT_CNT"."LineId", "_PM_BGT_CNT"."U_CardCode", "_PM_BGT_CNT"."U_Comments", "_PM_BGT_CNT"."U_ItemCode", "_PM_BGT_CNT"."U_M01Amnt", "_PM_BGT_CNT"."U_M02Amnt", "_PM_BGT_CNT"."U_M03Amnt", "_PM_BGT_CNT"."U_M04Amnt", "_PM_BGT_CNT"."U_M05Amnt", "_PM_BGT_CNT"."U_M06Amnt", "_PM_BGT_CNT"."U_M07Amnt ", "_PM_BGT_CNT"."U_M08Amnt", "_PM_BGT_CNT"."U_M09Amnt" FROM "Database"."dbo"."@PM_BGT_CNT" "_PM_BGT_CNT" INNER JOIN "Database"."dbo"."OACT" "OACT" ON "_PM_BGT_CNT"."U_SalesAcc"="OACT"."AcctCode"
My question is how to find were in my code the error is. There no line number or anything to tell me were the error is coming from.
Please help it's driving me mad!
Kind wishes,
Simon.
As can been seen there are four nests of tables, then union joined to each other.
The first three nests work well together but it's only when I bring in the fourth nest that this error occurs. The fourth nest work fine by itself but something goes wrong when it linked to the others.
UNION statements require that columns -- by their positions -- across all statements agree in terms of data type. Locate which column N in statement 4 has a different data type from column N in the previous statements.
Also, keep in mind that UNION removes duplicates (which is a slow process) while UNION ALL is much faster because it doesn't remove duplicates. If you don't need to remove duplicates, use UNION ALL.
