CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Shaji
Shaji

Reputation: 761

Conversion Failed

I am running into this issue trying to run a SQL query. Can't figure out where the conversion to "int" is happening. Can someone please point me in the right direction?

SELECT  TOP 100 PERCENT 
        a.S1 as "SegNumber", 
        a.S2 as "SegmentName", 
        a.S3 as "Description",
        a.S4 as "SegmentValue" 
FROM
( 
    SELECT      3 as "S1", 
                LTRIM(RTRIM(b.SUMMARY_ACCT)) + '_' + 'Accounts' as "S2", 
                b.ACCOUNT_DESC as "S3", 
                '^' + LTRIM(RTRIM(c.SUMMARY_ACCT)) + '_' + 'Accounts' as "S4" 
    FROM        TEST.dbo.GLCHARTSUM b 
    inner join  TEST.dbo.GLCHARTSUM c   ON  c.PARENT_OBJ_ID = b.SUMRY_ACCT_ID 
    UNION ALL 
    SELECT      3 as "S1", 
                LTRIM(RTRIM(c.SUMMARY_ACCT)) + '_' + 'Accounts' as "S2", 
                c.ACCOUNT_DESC as "S3", 
                LTRIM(RTRIM(convert(varchar(100),b.ACCOUNT))) as "S4" 
    FROM        TEST.dbo.GLCHARTDTL b 
    INNER JOIN  TEST.dbo.GLCHARTSUM c   ON  c.SUMRY_ACCT_ID = b.SUMRY_ACCT_ID 
    UNION ALL 
    SELECT      2 as "S1", 
                LTRIM(RTRIM(ACCT_UNIT)) + '_' + 'Unit' as "S2" , 
                DESCRIPTION as "S3", 
                '^' + LTRIM(RTRIM(ACCT_UNIT)) + '_Unit' as "S4" 
    FROM        TEST.dbo.GLNAMES 
    WHERE       POSTING_FLAG ='S' 
    UNION ALL 
    SELECT      2 as "S1", 
                LTRIM(RTRIM(ACCT_UNIT)) + '_' + 'Unit' as "S2", 
                DESCRIPTION as "S3", 
                LTRIM(RTRIM(ACCT_UNIT)) as "S4" 
    FROM        TEST.dbo.GLNAMES 
    WHERE       POSTING_FLAG ='P'
    UNION ALL 
    SELECT      3 as "S1", 
                MATRIX_CAT + Substring(MX_VALUE,1,5) AS "S2", 
                MX_VALUE as "S3", 
                ACCOUNT AS "S4" 
    FROM        TEST.dbo.GLACCMXVAL b 
    INNER JOIN  TEST.dbo.GLCHARTDTL c   ON  b.OBJ_ID = c.OBJ_ID 
) a 
WHERE   1 = 1 
ORDER BY a.S2

Error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '^ASSETS_Accounts' to data type int.

Upvotes: 0

Views: 134

Answers (1)

Siyual
Siyual

Reputation: 16917

The reason you're seeing this error is because of your last UNION ALL. ACCOUNT is an INT value, but the other S4 columns are VARCHAR.

You should cast your ACCOUNT column to a VARCHAR

...
UNION ALL 
SELECT      3 as "S1", 
            MATRIX_CAT + Substring(MX_VALUE,1,5) AS "S2", 
            MX_VALUE as "S3", 
            CONVERT(VARCHAR (100), ACCOUNT)  AS "S4" 
...

Upvotes: 3

Related Questions