Should I be using CASE ? - 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

General SQL Server Forums

New to SQL Server Programming

Should I be using CASE ?

Author

Topic

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 16:11:21

I have what seems to be a simple CASE statement, but it does not work. I Googled CASE SQL and looked at a variety of examples, but none seemed to match my case (pun intended). Here is the code:


SELECT
('0' + CONVERT (varchar (10), W.WorkerID)) AS VendorID,
"Vendor Name" = 
   CASE W.dba IS NOT NULL THEN WorkerID.dba
    ELSE  (W.FirstName + ' ' + W.LastName)
END,
 (W.FirstName + ' ' + W.LastName)  AS Contact

FROM tblWorker AS W

Am I going about this the right way? Basically, am wanting the data from column W.dba to be displayed, but if the column is NULL then put in the expression W.FirstName + W.LastName.

Thanks!
Dan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 16:17:12

You are almost there - you just needed a WHEN.

SELECT
('0' + CONVERT (varchar (10), W.WorkerID)) AS VendorID,
"Vendor Name" = 
   CASE WHEN W.dba IS NOT NULL THEN WorkerID.dba
    ELSE  (W.FirstName + ' ' + W.LastName)
END,
 (W.FirstName + ' ' + W.LastName)  AS Contact

FROM tblWorker AS W

Is there a possibility that FirstName or LastName or WorkerID can be null?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 16:28:43

Ok, I read your post a second time. So, you are getting some data, but it is giving you nulls. So, do this:

SELECT
('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID,
"Vendor Name" = 
   CASE WHEN W.dba IS NOT NULL THEN CONVERT(VARCHAR(64),WorkerID.dba)
    ELSE  (W.FirstName + ' ' + W.LastName)
END,
 (COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,''))  AS Contact

FROM tblWorker AS W

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 16:30:54

It doesn't like either one of those.

I can't imagine a circumstance when a first or last name could be null.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 16:32:10

[CODE]
SELECT
('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID,
CASE WHEN W.dba IS NOT NULL THEN CONVERT(VARCHAR(64),WorkerID.dba)
ELSE (W.FirstName + ' ' + W.LastName)
END AS [Vendor Name],
(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,'')) AS Contact

FROM tblWorker AS W

[/CODE]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 16:36:30

quote:
Originally posted by dwdwone

It doesn't like either one of those.

I can't imagine a circumstance when a first or last name could be null.

What are you seeing? An error message? Or no data at all? If you are getting an error message can you post the exact error message (including error code and all)?

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 16:40:04

She's hiccupping on that as well. Very strange. It seems like it should be pretty straight forward, which is why I thought maybe I should be using another command.

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 16:46:44

Unfortunately I have to use our vendor's interface, which seems to be cmd_click. I'm allowed to write code through their interface but I don't have direct access to the MSSQL command prompt. Here are the results though:

Proc Arguments
Empty =
Local Variables
msTableName =
strQuery = SELECT

CASE WHEN W.dba IS NOT NULL THEN CONVERT(VARCHAR(64),WorkerID.dba) ELSE (W.FirstName + ' ' + W.LastName) END AS [Vendor Name], (COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,'')) AS Contact

FROM tblWorker AS W

Module Variables
QueryField(0) =
QueryField(1) =
QueryField(2) =
QueryField(3) =
QueryFieldType(0) = 1
QueryFieldType(1) = 1
QueryFieldType(2) = 1
QueryFieldType(3) = 1
QueryCondition(0) =
QueryCondition(1) =
QueryCondition(2) =
QueryCondition(3) =
QueryValue(0) =
QueryValue(1) =
QueryValue(2) =
QueryValue(3) =
RetVal = 04779
IsCustomerLookup = False

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 16:48:16

Should the red highlighted "WorkerID" be W below?

quote:
Originally posted by MuMu88

[CODE]
SELECT
('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID,
CASE WHEN W.dba IS NOT NULL THEN CONVERT(VARCHAR(64),WorkerID.dba)
ELSE (W.FirstName + ' ' + W.LastName)
END AS [Vendor Name],
(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,'')) AS Contact

FROM tblWorker AS W

[/CODE]

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 17:05:07

Your eye is sharp. It should be W.dba, since WorkerID is the column, not the table. Which of course was an error in my original code. And now it works perfectly.

And I see that if I do not use COALESCE I don't get the expected results. so I will have to use that snippet and learn more about that command as this is the second time I've come across it.

Thank you!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 17:12:09

quote:
Originally posted by MuMu88

Should the red highlighted "WorkerID" be W below?
quote:
Originally posted by MuMu88

[CODE]
SELECT
('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID,
CASE WHEN W.dba IS NOT NULL THEN CONVERT(VARCHAR(64),WorkerID.dba)
ELSE (W.FirstName + ' ' + W.LastName)
END AS [Vendor Name],
(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,'')) AS Contact

FROM tblWorker AS W

[/CODE]

WTG!!

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 18:01:44

Yes. But I checked and the error was mine in the original code.

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 19:26:12

The other mistake I made was that the conditional should be IS NULL, not IS NOT NULL. With IS NOT NULL, no data ends up in the Vendor Name column.

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 19:41:12

OK, still not working although it does not crash. I'm using


SELECT W.WorkerID,
 ('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID, 

CASE 
  WHEN W.dba IS NOT NULL THEN CONVERT(VARCHAR(64),W.dba) 
  ELSE (W.FirstName + ' ' + W.LastName) END AS [Vendor Name],
   (COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,'')) AS Contact, 
FROM tblWorker AS W

If a person's name is john smith and w.dba is null it does not populate Vendor Name with John Smith, which is what I would expect. Instead, Vendor Name is Null.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 19:51:14

Try this:

quote:
Originally posted by dwdwone

OK, still not working although it does not crash. I'm using

SELECT W.WorkerID,
('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID,
"Vendor Name" =
CASE
WHEN W.dba IS NOT NULL THEN CONVERT(VARCHAR(64),W.dba)
ELSE (COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,'')) END,
(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,'')) AS Contact,
FROM tblWorker AS W

If a person's name is john smith and w.dba is null it does not populate Vendor Name with John Smith, which is what I would expect. Instead, Vendor Name is Null.

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-08 : 20:04:30

I'm still getting the same results. I tried reversing the logic but got the same results:


  WHEN W.dba IS NULL THEN (COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,''))
ELSE CONVERT(VARCHAR(64),W.dba) 
  END,

How might I post a screenshot of the results? I don't see any options for attaching an image.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 20:53:26

What happens if you take the case statement out completely, do this:

[CODE]

SELECT W.WorkerID,
('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID,
(W.FirstName + ' ' + W.LastName) AS [Vendor Name],
(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,'')) AS Contact,
FROM tblWorker AS W
[/CODE]

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-08-09 : 12:52:50

That one doesn't work for two reasons.

1. There is a comma after AS Contact which needs to be removed, and
2. The conditional column is gone (W.dba)

What I'm trying to do is this:

IF W.dba (a company name) exists, put it in the Vendor Name column.
IF W.dba does not exist, put the persons first and last name (W.FirstName and W.LastName) into Vendor Name.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-10 : 13:23:43

Lets try this:
[CODE]

SELECT W.WorkerID,
('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID,
"Vendor Name" = COALESCE(NULLIF(W.dba,''), NULLIF(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,''), ' '), 'None Specified'),
COALESCE(NULLIF(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,''), ' '), 'None Specified') AS Contact
FROM tblWorker AS W

[/CODE]

TEST CODE:
[CODE]

DECLARE @tblWorker TABLE (WorkerID INT, dba VARCHAR(10), FirstName VARCHAR(10), LastName VARCHAR(10));

INSERT INTO @tblWorker VALUES
(1, 'Test', 'Joe', 'Doe'),
(2, NULL, 'John', 'Smith'),
(3, NULL, 'Jim', NULL),
(4, 'Foo', Null, 'Tim'),
(5, '', 'Jen', 'Jenson'),
(6, ' ', Null, Null);
SELECT W.WorkerID,
('0' + COALESCE(CONVERT (varchar (10), W.WorkerID),'')) AS VendorID,
"Vendor Name" = COALESCE(NULLIF(W.dba,''), NULLIF(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,''), ' '), 'None Specified'),
COALESCE(NULLIF(COALESCE(W.FirstName,'') + ' ' + COALESCE(W.LastName,''), ' '), 'None Specified') AS Contact
FROM @tblWorker AS W
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources