Click here to Skip to main content
15,887,833 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: (untagged)
When adding "Free Text" to column 1 I get an error. How can I fix this?


SQL
SELECT CAST(T1. [Free_Text]  as varchar(250)), CardCode, CardName, PymntGroup, SUM(Balance) BALANCE, SUM(A) FUTURE, SUM(B) '0-30', SUM(C) '31-60', SUM(D) '61-90', SUM(E) '91-120', SUM(F) '121+' FROM (
SELECT CAST(T1. [Free_Text]  as varchar(250)),T1.CardCode, T1.CardName, T2.[PymntGroup], T0.RefDate, T0.Ref1 'Document_Number',
     CASE  WHEN T0.TransType=13 THEN 'Invoice'
          WHEN T0.TransType=14 THEN 'Credit Note'
          WHEN T0.TransType=30 THEN 'Journal'
          WHEN T0.TransType=24 THEN 'Receipt'
          END AS 'Document_Type',
     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate, getdate())<=-1),0) 'A'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>=0 and DateDiff(day, T0.DueDate,getdate())<=30),0) 'B'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>30 and DateDiff(day, T0.DueDate,getdate())<=60),0) 'C'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>60 and DateDiff(day, T0.DueDate,getdate())<=90),0) 'D'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>90 and DateDiff(day, T0.DueDate,getdate())<=120),0) 'E'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>=121),0) 'F'
FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
Left outer join  OCTG T2 On T1.GroupNum = T2.GroupNum
WHERE  T1.CardType = 'C' 
) T100
GROUP BY CARDCODE, CARDNAME, PymntGroup
ORDER BY CARDCODE
Posted
Updated 5-Apr-15 7:58am
v3

1 solution

Check your database, and make sure that your OCRD table contains a column called Free_Text
Is it in JDT1 or OCTG instead?
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900