SQL query - CASE command????

[whatryaat]

Hi There,

I am fairly new to SQL and can't seem to figure this out. I'm trying to run a query in query analyzer that compares two fields and depending on the content changes the value of one at run time.

Both fields exist in the same record and both criteria must be met to qualify for the change of the NAME field to VACANT. Otherwise nothing changes.

I wasn't sure if I had to use a Go and a select or not. Yeah - I'm really that new to SQL!!!

USE STAFF_COMP
CASE name
WHEN REG_TEMP='R' AND NAME=' ' (I've also considered the IS NULL option here!)
THEN NAME = 'VACANT' (I didn't think I needed an else as the else would be to leave it be!)
END

A collegue told me to use the CASE command as that usually works. It seems simple enough with one field at a time but with the two I'm lost.

If I've left out info that is required to help me please let me know.

Thanks!

[Phil Weber]

I would just use an UPDATE query:

UPDATE STAFF_COMP
SET [NAME]='VACANT'
WHERE REG_TEMP='R' AND [NAME]=' '
-- or NAME IS NULL, if that's what you want

[whatryaat]

Hi Phil - Thanks for the prompt reply. However my understanding of update is that it permanently changes the records. I only want to change the value in this query in this particular instance for a user that wants to see it in a report. I don't want the empty name field to have VACANT in it otherwise.

Can you clarify if that is what update does?

Thanks.

[Rajesh GGKTECH]

Try this,

SELECT ISNULL([Name], 'VACANT') AS [NAME]
FROM TableName
WHERE REG_TEMP='R'

Thanks
Rajesh Jonnalagadda

[jacobsebastian]

I think what you need is the following:

SELECT
CASE
WHEN WHEN REG_TEMP='R' AND ISNULL(NAME,'') ='' THEN 'VACANT'
ELSE NAME
END
FROM STAFF_COMP

This returns 'VACANT' if the NAME column contains NULL or empty string and REG_TEMP = 'R'. In all other cases, it returns the current value stored in the NAME column.

Jacob Sebastian, SQL Server MVP
http://blog.beyondrelational.com