SQL query - CASE command????


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: SQL query - CASE command????

  1. #1
    Join Date
    Feb 2009
    Posts
    2

    SQL query - CASE command????

    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!

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Feb 2009
    Posts
    2

    Update?

    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.

  4. #4
    Join Date
    Feb 2009
    Posts
    4
    Try this,

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

    Thanks
    Rajesh Jonnalagadda

  5. #5
    Join Date
    Jan 2009
    Posts
    4
    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

Similar Threads

  1. Replies: 0
    Last Post: 02-14-2008, 08:55 AM
  2. query tuning
    By dhaya in forum Database
    Replies: 11
    Last Post: 08-25-2003, 06:24 PM
  3. How do I put my function in an SQL query?
    By Dav in forum VB Classic
    Replies: 10
    Last Post: 03-08-2001, 12:20 PM
  4. Replies: 0
    Last Post: 11-22-2000, 10:42 AM
  5. how to write this SQL query and SQL assertion
    By Diana Perkins in forum Database
    Replies: 2
    Last Post: 11-22-2000, 10:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center