ERROR: SAP DBTech JDBC: [339]: invalid number: not a valid number string 1).

[gabinaya]

Hi,

Good Day,

I'm facing issue " [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;339 invalid number: not a valid number string 'NULL' 'Document' (RDOC)" in SQL query.

Please help me anyone.

SELECT T1."DocEntry", T1."DocNum",

CASE

WHEN T1."CashSum">0 and T1."CashSum"= T1."DocTotal" then 'Cash'

WHEN T1."CheckSum">0 and T1."CheckSum"= T1."DocTotal" then 'Cheque'

WHEN T1."TrsfrSum">0 and T1."TrsfrSum"= T1."DocTotal" then 'BankTransfer'

WHEN T1."CreditSum">0 and T1."CreditSum"= T1."DocTotal" then 'CreditCard'

ELSE 'Multiple'

END as "Payment",

T1."CardCode" AS "Customer Code", T1."CardName" AS "Customer Name", T1."DocDate" AS "Date", T1."DocDueDate" "Due Date", T0."SumApplied" AS "Amount Paid",(T0."SumApplied"-(T0."SumApplied"*ROUND(T2."VatSum"/T2."DocTotal",4))) AS "Amount Paid Ex VAT", T2."VatSum", T2."DocTotal" AS "Doc Amount",T2."DocNum" AS "Doc No", T3."SlpName" AS "Sales Employee", 'AR INVOICE-PAID' AS "TYPE", T4."BPLName" AS "Branch"

FROM RCT2 T0

INNER JOIN ORCT T1 ON T0."DocNum" = T1."DocEntry"

LEFT JOIN OINV T2 ON T0."DocEntry" = T2."DocEntry" AND T0."InvType" = T2."ObjType"

LEFT JOIN OSLP T3 ON T2."SlpCode" = T3."SlpCode"

LEFT JOIN OBPL T4 ON T1."BPLId" = T4."BPLId"

WHERE T0."InvType" = '13' AND T1."Canceled" = 'N' AND T1."DocDate" >= [%0] AND T1."DocDate" <= [%1] AND T4."BPLId" = [%2]

UNION ALL

---PAID OB**

SELECT T1."DocEntry", T1."DocNum",

CASE

WHEN T1."CashSum">0 and T1."CashSum"= T1."DocTotal" then 'Cash'

WHEN T1."CheckSum">0 and T1."CheckSum"= T1."DocTotal" then 'Cheque'

WHEN T1."TrsfrSum">0 and T1."TrsfrSum"= T1."DocTotal" then 'BankTransfer'

WHEN T1."CreditSum">0 and T1."CreditSum"= T1."DocTotal" then 'CreditCard'

ELSE 'Multiple'

END as "Payment",

T1."CardCode" AS "Customer Code", T1."CardName" AS "Customer Name", T1."DocDate" AS "Date", T1."DocDueDate" "Due Date", T0."SumApplied" AS "Amount Paid",(T0."SumApplied"-(T0."SumApplied"*ROUND(T2."VatSum"/T2."DocTotal",4))) AS "Amount Paid Ex VAT", T2."VatSum", T2."DocTotal" AS "Doc Amount",T2."DocNum" AS "Doc No", T3."SlpName" AS "Sales Employee", 'OP-BAL-PAID' AS "TYPE", T4."BPLName" AS "Branch"

FROM RCT2 T0

INNER JOIN ORCT T1 ON T0."DocNum" = T1."DocEntry"

LEFT JOIN OINV T2 ON T0."DocEntry" = T2."DocEntry" AND T0."InvType" = T2."ObjType"

LEFT JOIN OSLP T3 ON T2."SlpCode" = T3."SlpCode"

LEFT JOIN OBPL T4 ON T1."BPLId" = T4."BPLId"

WHERE T0."InvType" = '-2' AND T1."Canceled" = 'N' AND T1."DocDate" >= [%0] AND T1."DocDate" <= [%1] AND T4."BPLId" = [%2]

UNION ALL

---RECONCILED INV

SELECT 'NULL' AS "Internal Number", T1."ReconNum" AS "Document Number", 'Internal Reconciliation' AS "Payment", T0."ShortName" AS "Customer Code", T5."CardName" AS "Customer Name", T1."ReconDate" AS "Date", '1900-01-01' AS "Due Date", T0."ReconSum" AS "Amount Paid", (T0."ReconSum"-(T0."ReconSum"*ROUND(T2."VatSum"/T2."DocTotal",4))) AS "Amount Paid Ex VAT", T2."VatSum", T2."DocTotal" AS "Doc Amount", T2."DocNum" AS "Doc No", T3."SlpName" AS "Sales Employee", 'RECONCILIATION-AR' AS "TYPE", T4."BPLName" AS "Branch"

FROM ITR1 T0

INNER JOIN OITR T1 ON T0."ReconNum" = T1."ReconNum"

LEFT JOIN OINV T2 ON T0."SrcObjAbs" = T2."DocEntry" AND T0."SrcObjTyp" = T2."ObjType"

LEFT JOIN OSLP T3 ON T2."SlpCode" = T3."SlpCode"

LEFT JOIN OBPL T4 ON T2."BPLId" = T4."BPLId"

LEFT JOIN OCRD T5 ON T0."ShortName" = T5."CardCode"

WHERE T1."ReconType" = '0' AND T1."Canceled" = 'N' AND T0."SrcObjTyp" = '13' AND T1."ReconDate" >= [%0] AND T1."ReconDate" <= [%1] AND T4."BPLId" = [%2]

UNION ALL

---RECONCILED CN

SELECT 'NULL' AS "Internal Number", T1."ReconNum" AS "Document Number", 'Internal Reconciliation' AS "Payment", T0."ShortName" AS "Customer Code", T5."CardName" AS "Customer Name", T1."ReconDate" AS "Date", '1900-01-01' AS "Due Date", (T0."ReconSum"*-1) AS "Amount Paid", ((T0."ReconSum"-(T0."ReconSum"*ROUND(T2."VatSum"/T2."DocTotal",4)))*-1) AS "Amount Paid Ex VAT", (T2."VatSum"*-1), (T2."DocTotal"*-1) AS "Doc Amount", T2."DocNum" AS "Doc No", T3."SlpName" AS "Sales Employee", 'RECONCILIATION-CN' AS "TYPE", T4."BPLName" AS "Branch"

FROM ITR1 T0

INNER JOIN OITR T1 ON T0."ReconNum" = T1."ReconNum"

LEFT JOIN ORIN T2 ON T0."SrcObjAbs" = T2."DocEntry" AND T0."SrcObjTyp" = T2."ObjType"

LEFT JOIN OSLP T3 ON T2."SlpCode" = T3."SlpCode"

LEFT JOIN OBPL T4 ON T2."BPLId" = T4."BPLId"

LEFT JOIN OCRD T5 ON T0."ShortName" = T5."CardCode"

WHERE T1."ReconType" = '0' AND T1."Canceled" = 'N' AND T0."SrcObjTyp" = '14' AND T1."ReconDate" >= [%0] AND T1."ReconDate" <= [%1] AND T4."BPLId" = [%2]

UNION ALL

----RECONCILED OB**

SELECT 'NULL' AS "Internal Number", T1."ReconNum" AS "Document Number", 'Internal Reconciliation' AS "Payment", T0."ShortName" AS "Customer Code", T5."CardName" AS "Customer Name", T1."ReconDate" AS "Date", '1900-01-01' AS "Due Date", T0."ReconSum" AS "Amount Paid", (T0."ReconSum"*0) AS "Amount Paid Ex VAT", (T0."ReconSum"*0) AS "Total Tax", (T0."ReconSum"*0) AS "Doc Amount", T1."ReconNum" AS "Doc No", T3."SlpName" AS "Sales Employee", 'RECONCILIATION-OB' AS "TYPE", T4."BPLName" AS "Branch"

FROM ITR1 T0

INNER JOIN OITR T1 ON T0."ReconNum" = T1."ReconNum"

LEFT JOIN JDT1 T2 ON T0."TransId" = T2."TransId" AND T0."ShortName" = T2."ShortName"

LEFT JOIN OBPL T4 ON T2."BPLId" = T4."BPLId"

LEFT JOIN OCRD T5 ON T0."ShortName" = T5."CardCode"

LEFT JOIN OSLP T3 ON T5."SlpCode" = T3."SlpCode"

WHERE T1."ReconType" = '0' AND T1."Canceled" = 'N' AND T0."SrcObjTyp" = '-2' AND T1."ReconDate" >= [%0] AND T1."ReconDate" <= [%1] AND T4."BPLId" = [%2] AND T0."ShortName" LIKE '%C%' AND T0."IsCredit" = 'C'

UNION ALL

---STANDALONE CN

SELECT T1."DocEntry", T1."DocNum",

'NULL' as "Payment",

T1."CardCode" AS "Customer Code", T1."CardName" AS "Customer Name", T1."DocDate" AS "Date", T1."DocDueDate" AS "Due Date", ((T1."DocTotal"-T1."PaidSum")*-1) AS "Amount Paid",

(((T1."DocTotal"-T1."PaidSum")-((T1."DocTotal"-T1."PaidSum")*ROUND(T1."VatSum"/T1."DocTotal",4)))*-1) AS "Amount Paid Ex VAT",

(T1."VatSum"*-1) AS "Total Tax",(T1."DocTotal"*-1) AS "Doc Amount",T1."DocNum" AS "Doc No", T3."SlpName" AS "Sales Employee", 'CN-UNRECONCILED' AS "TYPE", T4."BPLName" AS "Branch"

FROM ORIN T1

LEFT JOIN OSLP T3 ON T1."SlpCode" = T3."SlpCode"

LEFT JOIN OBPL T4 ON T1."BPLId" = T4."BPLId"

WHERE T1."CANCELED" NOT IN ('Y','C') AND T1."DocDate" >= [%0] AND T1."DocDate" <= [%1] AND T4."BPLId" = [%2] AND T1."ObjType" = '14' AND T1."DocTotal"-T1."PaidSum" <> '0' AND T1."DocStatus" = 'O'

UNION ALL

---PAID CN

SELECT T1."DocEntry", T1."DocNum",

CASE

WHEN T1."CashSum">0 and T1."CashSum"= T1."DocTotal" then 'Cash'

WHEN T1."CheckSum">0 and T1."CheckSum"= T1."DocTotal" then 'Cheque'

WHEN T1."TrsfrSum">0 and T1."TrsfrSum"= T1."DocTotal" then 'BankTransfer'

WHEN T1."CreditSum">0 and T1."CreditSum"= T1."DocTotal" then 'CreditCard'

ELSE 'Multiple'

END as "Payment",

T1."CardCode" AS "Customer Code", T1."CardName" AS "Customer Name", T1."DocDate" AS "Date", T1."DocDueDate" "Due Date", (T0."SumApplied"*-1) AS "Amount Paid", (T0."SumApplied"-(T0."SumApplied"*-1*ROUND(T2."VatSum"/T2."DocTotal",4))) AS "Amount Paid Ex VAT", (T2."VatSum"*-1) AS "Total Tax",(T2."DocTotal"*-1) AS "Doc Amount",T2."DocNum" AS "Doc No", T3."SlpName" AS "Sales Employee", 'CN FROM INCOMING' AS "TYPE", T4."BPLName" AS "Branch"

FROM RCT2 T0

INNER JOIN ORCT T1 ON T0."DocNum" = T1."DocEntry"

LEFT JOIN ORIN T2 ON T0."DocEntry" = T2."DocEntry" AND T0."InvType" = T2."ObjType"

LEFT JOIN OSLP T3 ON T2."SlpCode" = T3."SlpCode"

LEFT JOIN OBPL T4 ON T1."BPLId" = T4."BPLId"

WHERE T0."InvType" = '14' AND T1."Canceled" = 'N' AND T1."DocDate" >= [%0] AND T1."DocDate" <= [%1] AND T4."BPLId" = [%2]

ORDER BY T1."DocNum"

[staseeb · Accepted solution]

Hello kedalene.chong,

Do not use 'NULL' AS "Internal Number" , as in other union queries it is an integere, you can use 0 or -1 instead.

Regards,

Taseeb Saeed

[gabinaya · Accepted solution]

Thank you for Valuable responce