SQL query - CASE command????
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!!!
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!)
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.
I would just use an UPDATE query:
WHERE REG_TEMP='R' AND [NAME]=' '
-- or NAME IS NULL, if that's what you want
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
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?
SELECT ISNULL([Name], 'VACANT') AS [NAME]
I think what you need is the following:
WHEN WHEN REG_TEMP='R' AND ISNULL(NAME,'') ='' THEN 'VACANT'
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
Last Post: 02-14-2008, 08:55 AM
By dhaya in forum Database
Last Post: 08-25-2003, 06:24 PM
By Dav in forum VB Classic
Last Post: 03-08-2001, 12:20 PM
By Anurag in forum Database
Last Post: 11-22-2000, 10:42 AM
By Diana Perkins in forum Database
Last Post: 11-22-2000, 10:14 AM
Tags for this Thread
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL