Link to home
Create AccountLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

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(VARCHAR(20),CAST(SUM(Remove) 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','24') 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','24') 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.  
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

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.
Avatar of dbaSQL

ASKER

well, i'm trying this:

CONVERT(VARCHAR(20),CAST(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','24') 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
Avatar of dbaSQL

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','24') THEN quantity ELSE 0 END
) AS 'Volume'

error converting data type varchar to bigint.
Avatar of dbaSQL

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.
ASKER CERTIFIED SOLUTION
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Glad you found it.  I couldn't see it either -- eyes get blurred after a while of staring at the screen and code. :)