Dumb question about using < in a select statement

Question

Post reply

Dumb question about using < in a select statement

Jim Muir

SSC Enthusiast

Points: 132
More actions
September 20, 2005 at 6:20 pm

#363696

I'm new to sql server, but queries in other servers allow this:
SELECT clinno, ssn, dateof, IIF(blue > 3, 'Depressed', 'OK') AS depressed
FROM [dbo].[ORYX_PPS]
Error in list of function arguments: '>' not recognized.
Unable to parse query text.
cannot use case..when.. statements either.
What am I missing?
Jim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Jim - I'm not sure why you said you cannot use case...when statements ?!?! Any particular reason....?!

Your query should work with:

SELECT clinno, ssn, dateof, Depressed = case when blue > 3 then 'Depressed' else 'OK' end

FROM [dbo].[ORYX_PPS]

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Also - I've personally not used the IIF function in Sql Server, but try changing your syntax to:

SELECT clinno, ssn, dateof, 'IIF(blue > 3, "Depressed", "OK")' AS depressed

FROM [dbo].[ORYX_PPS]

**ASCII stupid question, get a stupid ANSI !!!**

Papillon Hall of Fame Points: 3303 More actions · Answer 3

hi!!

you canot use iif in sql server but you can you equivalent to iif as CASE or DECODE

and your code might be like this

SELECT clinno, ssn, dateof, Depressed = case when blue > 3 then 'Depressed' else 'OK' end

FROM [dbo].[ORYX_PPS]

as SUShILA told you

shashank

Regards,

Papillon

Jim Muir SSC Enthusiast Points: 132 More actions · Answer 4

Found the solution...

SELECT clinno, ssn, dateof, Depressed = CASE WHEN blue > 3 THEN 'Depressed' ELSE 'OK' END

FROM [dbo].[ORYX_PPS]

produces an error, but its not the query statement, but the designer that fudges.

Its not the statements that are problematic, just the query pane. The error is not the statement but the fact that the query pane can't handle the statement. Close the query pane and ba ba bing... query runs.

Thanks everyone, I thought I was losing my mind. What a lame implementation of query builder that renders and error that makes you believe you have a fouled statement when it is the GUI that is poorly implemented. Good checking would render an error such as:

" The query syntax is correct, but the query pane cannot handle the statement, try closing the query pane and running the query again."

Thanks again!

Jim

scking SSCrazy Points: 2598 More actions · Answer 5

I'm not sure why posts are assigning the value of the case statement to depressed but it is simply an alias. The SQL below in more in line with assigning the correct values to the alias name. By the way, I used to do IIFs in code and believe me the CASE statement is a GREAT!! improvement. Deeply nested IIF statements are impossible to read but the CASE statement is easy.

SELECT clinno, ssn, dateof, CASE WHEN blue blue > 3 THEN 'Depressed' ELSE 'OK' END AS depressed

FROM [dbo].[ORYX_PPS]