<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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
pssandhuCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor Commented:
Hi pssandhu,

Thank you very much for the information.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.