Error When Using Cast - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error When Using Cast

Author

Topic

rahee1
Starting Member

6 Posts

Posted - 2007-12-10 : 08:03:29

Hi, I'm very new to SQL. Trying to sum two fields, but I have to change the datatype first. Here's the code and the error message I receive. Any help would be appreciated.

SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(TS_USER_07 * TS_STATUS) AS value
FROM TEST_54_VW
GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07
WHERE CAST(TS_USER_07 AS INTEGER)

Incorrect syntax near the keyword 'WHERE'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-10 : 08:04:33

quote:
Originally posted by rahee1

Hi, I'm very new to SQL. Trying to sum two fields, but I have to change the datatype first. Here's the code and the error message I receive. Any help would be appreciated.

SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(TS_USER_07 * TS_STATUS) AS value
FROM TEST_54_VW
WHERE CAST(TS_USER_07 AS INTEGER) = <some value>
GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07
~~WHERE CAST(TS_USER_07 AS INTEGER)~~

Incorrect syntax near the keyword 'WHERE'.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 08:11:44

or

SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(CAST(TS_USER_07 AS INTEGER) * TS_STATUS) AS value
FROM TEST_54_VW
GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07

Madhivanan

Failing to plan is Planning to fail

rahee1
Starting Member

6 Posts

Posted - 2007-12-10 : 08:12:36

I'm confused as to where you say = <some value>. I thought the cast function ended once you I what datatype to convert to. Could you elaborate on this please?

quote:
Originally posted by harsh_athalye

quote:
Originally posted by rahee1

Hi, I'm very new to SQL. Trying to sum two fields, but I have to change the datatype first. Here's the code and the error message I receive. Any help would be appreciated.

SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(TS_USER_07 * TS_STATUS) AS value
FROM TEST_54_VW
WHERE CAST(TS_USER_07 AS INTEGER) = <some value>
GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07
~~WHERE CAST(TS_USER_07 AS INTEGER)~~

Incorrect syntax near the keyword 'WHERE'.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-10 : 08:13:51

Think you might want
SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(convert(int,TS_USER_07) * TS_STATUS) AS value
FROM TEST_54_VW
GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rahee1
Starting Member

6 Posts

Posted - 2007-12-10 : 08:16:32

I know that I will have convert both TS_STATUS and TS_USER_07 to INTEGER because they're both of type varchar. My example wasn't the complete code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 08:19:33

quote:
Originally posted by rahee1

I know that I will have convert both TS_STATUS and TS_USER_07 to INTEGER because they're both of type varchar. My example wasn't the complete code.

If both have to be part of arithmetic operation, then why dont you chane the datatype to INT?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 08:19:53

quote:
Originally posted by rahee1

My example wasn't the complete code.

Then maybe all answers above was not complete also?

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-10 : 08:20:02

quote:
Originally posted by rahee1

I'm confused as to where you say = <some value>. I thought the cast function ended once you I what datatype to convert to. Could you elaborate on this please?

quote:
Originally posted by harsh_athalye

quote:
Originally posted by rahee1

Hi, I'm very new to SQL. Trying to sum two fields, but I have to change the datatype first. Here's the code and the error message I receive. Any help would be appreciated.

SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(TS_USER_07 * TS_STATUS) AS value
FROM TEST_54_VW
WHERE CAST(TS_USER_07 AS INTEGER) = <some value>
GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07
~~WHERE CAST(TS_USER_07 AS INTEGER)~~

Incorrect syntax near the keyword 'WHERE'.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

The cast will produce a value so you will end up with something like

where 2

That's not a valid where clause as it has to be a boolean
so
WHERE CAST(TS_USER_07 AS INTEGER) = 2

or maybe you want something more like
where isnumeric(TS_USER_07) = 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rahee1
Starting Member

6 Posts

Posted - 2007-12-10 : 08:20:52

Honestly, I would love that. However, I have no control over that in this situation.

quote:
Originally posted by madhivanan

quote:
Originally posted by rahee1

I know that I will have convert both TS_STATUS and TS_USER_07 to INTEGER because they're both of type varchar. My example wasn't the complete code.

If both have to be part of arithmetic operation, then why dont you chane the datatype to INT?

Madhivanan

Failing to plan is Planning to fail

rahee1
Starting Member

6 Posts

Posted - 2007-12-10 : 08:22:58

The only thing that wasn't complete was that I forgot to mention that both fields in the sum function had to be converted from varchar to integer.

quote:
Originally posted by Peso

quote:
Originally posted by rahee1

My example wasn't the complete code.
Then maybe all answers above was not complete also?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 08:58:24

Did you arrive at the query finally?

Madhivanan

Failing to plan is Planning to fail

rahee1
Starting Member

6 Posts

Posted - 2007-12-10 : 09:05:45

Sorry, I'm realizing I made a few mistakes in explaining things when I initially posted this topic.

The following code will return 4 colums, TS_USER_07 and VALUE being numeric.

SELECT TS_RESPONSIBLE, TS_USER_07, TS_STATUS, COUNT (*) VALUE
FROM TEST_54_VW
GROUP BY TS_RESPONSIBLE, TS_USER_07, TS_STATUS

I need to now multiply those two (TS_USER_07 and value).

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-10 : 10:41:33

select TS_RESPONSIBLE, TS_USER_07, TS_STATUS, VALUE, convert(int,TS_USER_07) * VALUE
from
(SELECT TS_RESPONSIBLE, TS_USER_07, TS_STATUS, COUNT (*) VALUE
FROM TEST_54_VW
GROUP BY TS_RESPONSIBLE, TS_USER_07, TS_STATUS
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources