Conditional Views - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Conditional Views

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-02-04 : 04:09:25

I am using SQL server 7 and want to write a conditional statement (ideally to be used in a view).

What I want is something like this

If the value in field is null then return 'unknown'
else
if the value in field is 'a' then return 'yes'
else
if the value in field is not 'a' then return 'no'

I know I can do this sort of thing in Access but I really need it to be in a view so that I can restrict access to it and because it needs to be part of a select statement. I've tried using Case statements but get an error. Am I going to have to use a stored procedure?

Thanks in advance

steve (elwoosAThotmail.com)

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-02-04 : 04:18:54

A case expression should be ideal.

select case when field is null then 'Unknown'
when field = 'a' then 'yes'
else 'no' end from t

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-04 : 04:18:59

select case when fld is null then 'unknown' when fld = 'a' then 'yes' else 'no' end
from tbl

hah - he wants a small 'u' in unknown

==========================================
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.

Edited by - nr on 02/04/2003 04:22:47

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-02-04 : 04:24:36

Does CASE work in a view?

steve

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-04 : 04:27:15

Yes - it's available in the select statement.

==========================================
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.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-02-04 : 08:41:46

When I try to put a csae statement into a view I get the error

CASE is not supported

Do you know what I am doing wrong. below is the sample

SELECT PTNO,
CASE
WHEN PTNO < 3000 THEN 'low'
WHEN PTNO >3000 then 'high'
else 'Middle'
end as category
FROM PATIENT

thanks

steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-04 : 08:50:55

For some reason, you can't use CASE in the graphical VIEW creator. You need to type in the SQl using the query analyzer.

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-02-04 : 13:57:12

quote:

When I try to put a csae statement into a view I get the error

CASE is not supported

It is not an error message, but an information message. Like Jeff said, its not supported only by the graphical query designer, but your query should work.

OS

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-02-06 : 05:16:50

It worked!

I can create the view using Query Analyzer. Mildly irritating but at least I can do what I want

Thanks very much for all you help

steve

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-02-06 : 10:45:41

You can create the view using the graphical tool as well, just type in the CASE in the SQL window, and click the run button. You will get the error, but the query will run properly. Just ignore the messages.

BTW, it will yell for Union queries as well, "Union Is Not Supported", but they actually do work.

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-06 : 16:40:24

Just Curious, do the graphical interfaces have a significant impact on resources (memory, i/o, locks, ect)? Also doing table design? I'm a (mostly) pure Query Analyzer kind of guy.

Anyu info on this anywhere?

Thanks

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources