<Unsupported Data Type> (Query Problem)

Hi,

I have query below.

The problem is at column "AccNo", it shows <Unsupported Data Type> at all the rows.

What's wrong with my code?

Thank you.
WITH JobCTE AS (SELECT     A.AccNo, COALESCE (SUM(A.SaldoAwalDbt), 0) AS SaldoAwalDbt, COALESCE (SUM(A.SaldoAwalKrd), 0) AS SaldoAwalKrd, 
                                                              COALESCE (SUM(A.Debet01), 0) AS MutasiDebet, COALESCE (SUM(A.Kredit01), 0) AS MutasiKredit, B.GlobalAcc
                                       FROM         THLEDGER2008 AS A LEFT OUTER JOIN
                                                              TMGLACCNO AS B ON A.AccNo = B.AccNo LEFT OUTER JOIN
                                                              TMGLTYPE AS C ON C.GLTypeCode = B.GLTypeCode
                                       WHERE     (B.Global <> 'Y') AND (C.Tipe = 'N') AND (B.GLTypeCode = 'AS')
                                       GROUP BY A.AccNo, B.GlobalAcc)
    SELECT     A.GlobalAcc AS AccNo, B.Name, SUM(A.SaldoAwalDbt) AS SaldoAwalDbt, SUM(A.SaldoAwalKrd) AS SaldoAwalKrd, SUM(A.MutasiDebet) 
                            AS MutasiDebet, SUM(A.MutasiKredit) AS MutasiKredit, B.GlobalAcc, C.Saldo
     FROM         JobCTE AS A LEFT OUTER JOIN
                            TMGLACCNO AS B ON A.GlobalAcc = B.AccNo LEFT OUTER JOIN
                            TMGLTYPE AS C ON C.GLTypeCode = B.GLTypeCode
     GROUP BY A.GlobalAcc, B.Name, B.GlobalAcc, C.Saldo

Open in new window

LVL 1
emi_sastraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
pssandhuConnect With a Mentor Commented:
Although this might not make a difference but may be some null values are throwing off your query. Try using ISNULL to default the value from Null to something else. Something like this:
ON ISNULL(A.GlobalAcc,'00000000000') = ISNULL(B.AccNo, '00000000000')
Also, you are not by any chance using Visual Database Tools and working with data in "Result Pane". I found some reference regarding that:
http://msdn.microsoft.com/en-us/library/ms172022(VS.80).aspx
If so, check out the discussion under the last  heading "What cannot be represented Full".
Hope this helps.
P.
0
 
pssandhuCommented:
My first guess would be that the data types for columns GlobalAcc and AccNo are different. Can you and check and confirm if this is the case?
To avoid this you can use CAST() function to cast the other column to the correct data type. If your GlobalAcc is of type nvarchar and Accno as INT then we can cast AccNo to nvarchar. Something like this (see you join statement in the second query):


WITH JobCTE AS (SELECT     A.AccNo, COALESCE (SUM(A.SaldoAwalDbt), 0) AS SaldoAwalDbt, COALESCE (SUM(A.SaldoAwalKrd), 0) AS SaldoAwalKrd, 
                                                              COALESCE (SUM(A.Debet01), 0) AS MutasiDebet, COALESCE (SUM(A.Kredit01), 0) AS MutasiKredit, B.GlobalAcc
                                       FROM         THLEDGER2008 AS A LEFT OUTER JOIN
                                                              TMGLACCNO AS B ON A.AccNo = B.AccNo LEFT OUTER JOIN
                                                              TMGLTYPE AS C ON C.GLTypeCode = B.GLTypeCode
                                       WHERE     (B.Global <> 'Y') AND (C.Tipe = 'N') AND (B.GLTypeCode = 'AS')
                                       GROUP BY A.AccNo, B.GlobalAcc)
 
    SELECT     A.GlobalAcc AS AccNo, B.Name, SUM(A.SaldoAwalDbt) AS SaldoAwalDbt, SUM(A.SaldoAwalKrd) AS SaldoAwalKrd, SUM(A.MutasiDebet) 
                            AS MutasiDebet, SUM(A.MutasiKredit) AS MutasiKredit, B.GlobalAcc, C.Saldo
     FROM         JobCTE AS A LEFT OUTER JOIN
                            TMGLACCNO AS B ON A.GlobalAcc = Cast(B.AccNo as nvarchar(50)) LEFT OUTER JOIN
                            TMGLTYPE AS C ON C.GLTypeCode = B.GLTypeCode
     GROUP BY A.GlobalAcc, B.Name, B.GlobalAcc, C.Saldo

Open in new window

0
 
emi_sastraAuthor Commented:
Hi pssandhu,

The are the same type.

AccNo      char(11)       
Name      char(70)       
Global      char(1)       
GlobalAcc      char(11)       
GLTypeCode      char(2)       
Propotional      char(1)       
CrtId      char(10)       
CrtDate      datetime       
UpdId      char(10)       
UpdDate      datetime       

Weird, some times problem, sometimes not.

Thank you.
0
 
emi_sastraAuthor Commented:
Hi pssandhu,

Thank you very much for the information.
0
All Courses

From novice to tech pro — start learning today.