dbaSQL
asked on
error from varchar to bigint
yesterday i put a piece together for a summation, worked just fine. today i tried to add one more sum into it, and it keeps failing with msg 8114 -- error converting data type varchar to bigint. here is the proc before my change:
SELECT
Symbol,
EndPoint,
'Add'=LEFT(CONVERT(VARCHAR (20),CAST( SUM(Adds) AS MONEY), 1),LEN(CONVERT(VARCHAR(20) ,CAST(SUM( Adds) AS MONEY), 1)) - 3),
'Remove'=LEFT(CONVERT(VARC HAR(20),CA ST(SUM(Rem ove) AS MONEY), 1),LEN(CONVERT(VARCHAR(20) ,CAST(SUM( Remove) AS MONEY), 1)) - 3),
Volume
FROM (
SELECT
Symbol,
EndPoint,
SUM(CASE WHEN lflag IN ('A','17','18','25','26',' 27','28',' 43','44') THEN quantity ELSE 0 END) AS 'Adds',
SUM(CASE WHEN lflag IN ('R','N','02','21','22','2 4') THEN quantity ELSE 0 END) AS 'Remove',
LEFT(CONVERT(VARCHAR(20), CAST(SUM(quantity) AS MONEY), 1),LEN(CONVERT(VARCHAR(20) ,CAST(SUM( quantity) AS MONEY), 1)) - 3) AS Volume
FROM
......... t WITH (NOLOCK)
WHERE
blah blah blah
GROUP BY
Symbol,
EndPoint ) ev
GROUP BY
Symbol,
EndPoint,
Volume
UNION ALL
SELECT
Symbol,
' ' AS EndPoint,
' ' AS 'Add',
' ' AS 'Remove',
' ' AS 'Volume'
FROM
........... WITH (NOLOCK)
WHERE
blah blah blah.....
GROUP BY
Symbol
ORDER BY
Symbol,
EndPoint,
Volume
this is a sample result:
symbol endpoint adds remove volume
EWJ
EWJ AAAA 77,411 0 77,411
EWJ BBBB 34,190 3,300 37,490
SIRI
SIRI AAAA 30,400 14,100 44,500
SIRI BBBB 45,100 29,400 80,400
i am trying to change it to total that volume on the far right -- on the top of each distinct symbol line, like this:
EWJ 114,901
EWJ AAAA 77,411 0 77,411
EWJ BBBB 34,190 3,300 37,490
SIRI 124,900
SIRI AAAA 30,400 14,100 44,500
SIRI BBBB 45,100 29,400 80,400
i tried adding this to that bottom select --
(SUM(CASE WHEN lflag IN ('A','17','18','25','26',' 27','28',' 43','44') THEN quantity ELSE 0 END) +
SUM(CASE WHEN lflag IN ('R','N','02','21','22','2 4') THEN quantity ELSE 0 END)) AS 'Volume'
it works by itself, but it fails when i try to run the whole piece together --- the upper unioned to the lower.
SELECT
Symbol,
EndPoint,
'Add'=LEFT(CONVERT(VARCHAR
'Remove'=LEFT(CONVERT(VARC
Volume
FROM (
SELECT
Symbol,
EndPoint,
SUM(CASE WHEN lflag IN ('A','17','18','25','26','
SUM(CASE WHEN lflag IN ('R','N','02','21','22','2
LEFT(CONVERT(VARCHAR(20), CAST(SUM(quantity) AS MONEY), 1),LEN(CONVERT(VARCHAR(20)
FROM
......... t WITH (NOLOCK)
WHERE
blah blah blah
GROUP BY
Symbol,
EndPoint ) ev
GROUP BY
Symbol,
EndPoint,
Volume
UNION ALL
SELECT
Symbol,
' ' AS EndPoint,
' ' AS 'Add',
' ' AS 'Remove',
' ' AS 'Volume'
FROM
........... WITH (NOLOCK)
WHERE
blah blah blah.....
GROUP BY
Symbol
ORDER BY
Symbol,
EndPoint,
Volume
this is a sample result:
symbol endpoint adds remove volume
EWJ
EWJ AAAA 77,411 0 77,411
EWJ BBBB 34,190 3,300 37,490
SIRI
SIRI AAAA 30,400 14,100 44,500
SIRI BBBB 45,100 29,400 80,400
i am trying to change it to total that volume on the far right -- on the top of each distinct symbol line, like this:
EWJ 114,901
EWJ AAAA 77,411 0 77,411
EWJ BBBB 34,190 3,300 37,490
SIRI 124,900
SIRI AAAA 30,400 14,100 44,500
SIRI BBBB 45,100 29,400 80,400
i tried adding this to that bottom select --
(SUM(CASE WHEN lflag IN ('A','17','18','25','26','
SUM(CASE WHEN lflag IN ('R','N','02','21','22','2
it works by itself, but it fails when i try to run the whole piece together --- the upper unioned to the lower.
Try removing the single quotes (') from column names. Use double quotes (") or bracket ([ ]) and see if it works. Since the add/remove fields are money fields on top query, you can try using 0 instead of ''. Just a thought.
ASKER
well, i'm trying this:
CONVERT(VARCHAR(20),CAST(S UM(CASE WHEN lflag IN ('A','17','18','25','26',' 27','28',' 43','44') THEN quantity ELSE 0 END) +
SUM(CASE WHEN lflag IN ('R','N','02','21','22','2 4') THEN quantity ELSE 0 END) AS MONEY))
it computes, but my numbers are way wrong. the sample i'm looking at now has:
91,276
56,479
94,083
75,459
148,999
i'd expect 466,296
i'm getting 390,837
CONVERT(VARCHAR(20),CAST(S
SUM(CASE WHEN lflag IN ('R','N','02','21','22','2
it computes, but my numbers are way wrong. the sample i'm looking at now has:
91,276
56,479
94,083
75,459
148,999
i'd expect 466,296
i'm getting 390,837
ASKER
this fails w/the msg 8114, too:
SUM (
CASE WHEN lflag IN ('A','17','18','25','26',' 27','28',' 43','44') THEN quantity ELSE 0 END +
CASE WHEN lflag IN ('R','N','02','21','22','2 4') THEN quantity ELSE 0 END
) AS 'Volume'
error converting data type varchar to bigint.
SUM (
CASE WHEN lflag IN ('A','17','18','25','26','
CASE WHEN lflag IN ('R','N','02','21','22','2
) AS 'Volume'
error converting data type varchar to bigint.
ASKER
and, i realized since i am trying to get one summation of all counts per symbol, the two cases aren't needed. so....
i'm trying just one big one, it fails, same error.
SUM (
CASE WHEN lflag IN ('A','17','18','25','26',' 27','28',' 43','44',' R','N','02 ','21','22 ','24') THEN quantity ELSE 0 END
) AS 'Volume'
but it only fails whence in the union. it runs just fine by itself.
i'm trying just one big one, it fails, same error.
SUM (
CASE WHEN lflag IN ('A','17','18','25','26','
) AS 'Volume'
but it only fails whence in the union. it runs just fine by itself.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Glad you found it. I couldn't see it either -- eyes get blurred after a while of staring at the screen and code. :)