cancel
Showing results for 
Search instead for 
Did you mean: 

Query for Customer outstanding balance with Address

Former Member
0 Kudos

Hi Experts,

I executing the below query and i am getting below error. how to fix this?

SELECT T1.[ShortName], T2.[CardName], T1.[Account], sum(T1.[Debit]) as 'Debit', sum(T1.[Credit]) as 'Credit',    sum(T1.[Debit])- sum(T1.[Credit]) as 'Balance', T3.[Address], T3.[Street], T3.[Block], T3.[StreetNo], T3.[Building], T3.[City], T3.[State],T3.[Country], T3.[ZipCode] FROM dbo.OJDT T0 

INNER JOIN dbo.JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN dbo.OCRD T2 on T2.CardCode = T1.[ShortName] INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode WHERE T2.[CardType] ='c' and

T2.[Balance] !=0 and

T3.[AdresType] ='b' GROUP BY T1.[ShortName], T2.[CardName], T1.[Account],T3.[Address], T3.[Street], T3.[Block], T3.[StreetNo], T3.[Building], T3.[City], T3.[State],T3.[Country], T3.[ZipCode]

Error

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.

Regards,

Dwarak

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

SELECT T1.[ShortName], T2.[CardName], T1.[Account], sum(T1.[Debit]) as 'Debit', sum(T1.[Credit]) as 'Credit',    sum(T1.[Debit])- sum(T1.[Credit]) as 'Balance', cast(T3.[Address] as nvarchar (200)), cast(T3.[Street] as nvarchar (200)), cast(T3.[Block] as nvarchar (200)), cast(T3.[StreetNo] as nvarchar (200)), cast(T3.[Building]as nvarchar (200)), cast(T3.[City] as nvarchar (200)), cast(T3.[State]as nvarchar (200)), cast(T3.[Country] as nvarchar (200)), cast(T3.[ZipCode]as nvarchar (200))  FROM dbo.OJDT T0 

INNER JOIN dbo.JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN dbo.OCRD T2 on T2.CardCode = T1.[ShortName] INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode WHERE T2.[CardType] ='c' and

T2.[Balance] !=0 and

T3.[AdresType] ='b' GROUP BY T1.[ShortName], T2.[CardName], T1.[Account], cast(T3.[Address] as nvarchar (200)), cast(T3.[Street] as nvarchar (200)), cast(T3.[Block] as nvarchar (200)), cast(T3.[StreetNo] as nvarchar (200)), cast(T3.[Building]as nvarchar (200)), cast(T3.[City] as nvarchar (200)), cast(T3.[State]as nvarchar (200)), cast(T3.[Country] as nvarchar (200)), cast(T3.[ZipCode]as nvarchar (200))

Hope helpful.

Answers (1)

Answers (1)

KennedyT21
Active Contributor
0 Kudos

Try This

SELECT T1.[ShortName], T2.[CardName], T1.[Account],

sum(T1.[Debit]) as 'Debit', sum(T1.[Credit]) as 'Credit',  

sum(T1.[Debit])- sum(T1.[Credit]) as 'Balance',

T3.[Address], T3.[Street], T3.[Block], T3.[StreetNo],

T3.[Building], T3.[City], T3.[State],T3.[Country], T3.[ZipCode]

FROM dbo.OJDT T0

INNER JOIN dbo.JDT1 T1 ON T0.TransId = T1.TransId

INNER JOIN dbo.OCRD T2 on T2.CardCode = T1.[ShortName]

INNER JOIN CRD1 T3 ON T2.CardCode = T3.CardCode

WHERE T2.[CardType] ='C' and

T2.[Balance] <>'0' and

T3.[AdresType] ='B'

GROUP BY T1.[ShortName], T2.[CardName], T1.[Account],T3.[Address],

T3.[Street], T3.[Block], T3.[StreetNo], T3.[Building], T3.[City],

T3.[State],T3.[Country], T3.[ZipCode]

Regards

Kennedy