Skip to Content
0
May 18, 2016 at 08:47 PM

Customer Query showing aging with Invoice details error "Ambiguous Column Name"

324 Views

I want to add Invoice line details to this query so I added two tables OINV and INV1 which gave me an error "Ambiguous Column name Tax Date" Can anyone assist. SELECT T1.CardCode AS 'Cust Num', T1.CardName AS 'Cust Name', T0.SysDeb AS 'Debit Amt', T0.SysCred * -1 AS 'Credit Amt', CASE WHEN T0.TransType = 13 THEN 'AR Invoice' WHEN T0.TransType = 14 THEN 'AR Cred Memo' WHEN T0.TransType = 24 THEN 'Payment' ELSE 'Other' END AS 'Trans Type', T0.Ref1 AS 'Reference', FcCurrency AS 'Currency', CONVERT(VARCHAR(10), RefDate, 103) 'Posting Date', CONVERT(VARCHAR(10), DueDate, 103) 'Due Date', CONVERT(VARCHAR(10), TaxDate, 103) 'Doc Date' , CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 0 THEN SysCred * -1 ELSE SysDeb END END AS '0-30 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 30 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 61 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '31 to 60 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 60 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 91 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '61 to 90 days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 90 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 121 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '90 to 120 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 120 THEN CASE WHEN SysCred != 0 THEN SysCred * -1 ELSE SysDeb END END AS '120 Plus Days' FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode AND T1.CardType = 'C' INNER JOIN INV1 T2 ON T1.CardCode = T2.BaseCard INNER JOIN OINV T3 ON T2.DocEntry = T3.DocEntry WHERE T0.IntrnMatch = '0' AND T0.BalDueDeb != T0.BalDueCred and T1.CardCode Like '%[%0]%' ORDER BY T1.CardCode, T0.TaxDate FOR BROWSE