USING 'CASE' TO GROUP DATA

[PB]

Hi,

Can anyone please help me with SQL syntax to create a second variable
based on the value of another (both numeric)?

My effort is below but I get a syntax error.

SELECT

charA,

CASE charA
WHEN < -199 THEN 2
WHEN < 31 THEN 3
WHEN < 82 THEN 4
WHEN < 100 THEN 5
WHEN < 105 THEN 6
WHEN < 111 THEN 7
WHEN < 143 THEN 8
WHEN < 165 THEN 9
WHEN < 233 THEN 10
WHEN >= 233 THEN 11
END AS Bin_charA

FROM X
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '<'.

[Ed Murphy]

On 22 Aug 2006 01:19:18 -0700, "PB" <ph**********@hotmail.comwrote:

>Hi,

Can anyone please help me with SQL syntax to create a second variable
based on the value of another (both numeric)?

My effort is below but I get a syntax error.

SELECT

charA,

CASE charA
WHEN < -199 THEN 2
WHEN < 31 THEN 3
WHEN < 82 THEN 4
WHEN < 100 THEN 5
WHEN < 105 THEN 6
WHEN < 111 THEN 7
WHEN < 143 THEN 8
WHEN < 165 THEN 9
WHEN < 233 THEN 10
WHEN >= 233 THEN 11
END AS Bin_charA

FROM X
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '<'.

case
when CharA < -199 then 2
when CharA < 31 then 3
<etc.>

[rcamarda]

Your case structure is ok, btw you are missing a column title.
however, it looks like you are trying to compare a char type to an int
type.
Your numbers are not quoted to convert them to char, so.
either cast or charA to an int, or quote your values in the when.
HTH
Rob
PB wrote:

Hi,

Can anyone please help me with SQL syntax to create a second variable
based on the value of another (both numeric)?

My effort is below but I get a syntax error.

SELECT

charA,

CASE charA
WHEN < -199 THEN 2
WHEN < 31 THEN 3
WHEN < 82 THEN 4
WHEN < 100 THEN 5
WHEN < 105 THEN 6
WHEN < 111 THEN 7
WHEN < 143 THEN 8
WHEN < 165 THEN 9
WHEN < 233 THEN 10
WHEN >= 233 THEN 11
END AS Bin_charA

FROM X
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '<'.

[rcamarda]

oops, missed that you put 'AS' at then of case stucture.
PB wrote:

Hi,

Can anyone please help me with SQL syntax to create a second variable
based on the value of another (both numeric)?

WHEN >= 233 THEN 11

END AS Bin_charA