Case statement not behaving correctly

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 2008 Forums

Transact-SQL (2008)

Case statement not behaving correctly

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2013-08-02 : 20:33:52

I have data that looks like the following:

Vendor_Name Vendor_Alias
ABBOTT LABORATORIES INC (BOX 92679) ABBOTT LABORATORIES INC
ABB OPTICAL GROUP
ACTION COMMUNICATIONS INC NULL
ADI (POB 731340) ADI
AMER MEDICAL RESPONSE(POB 749667) AMERICAN MEDICAL RESPONSE WEST

If you notice, 3 of the records have both a vendor name and a vendor alias. One record (Actions Communications Inc) has a null for the Vendor Alias and one record (ABB Optical Group) has a blank for the Vendor Alias.

What I want to do is use the Vendor Alias if one exists. If it is null or blank I want to use the vendor name.

The case statement is as follows:

CASE
when RemitVendor.ALIAS = NULL then RemitVendor.NAME
when RemitVendor.ALIAS = '' then RemitVendor.NAME
else RemitVendor.ALIAS
END VENDOR_NAME,

However, for some bizarre reason, only SOME of the records are doing this correctly. Some records pull the null from the Vendor Alias when it should be displaying the Vendor Name.

From what I can see if the Vendor Alias is not blank or is not null then it is pulling the information correctly from the Vendor Alias. It is only when the Vendor Alias has a NULL or a blank that it isn't pulling the Vendor Name.

WHAT could be wrong with my case statement????

Thanks
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-02 : 20:46:22

[CODE]

-- TEST DATA
DECLARE @Temp TABLE (NAME VARCHAR(60), ALIAS VARCHAR(60));
INSERT INTO @Temp VALUES
('ABBOTT LABORATORIES INC (BOX 92679)', 'ABBOTT LABORATORIES INC'),
('ABB OPTICAL GROUP', ''),
('ACTION COMMUNICATIONS INC', NULL),
('ADI (POB 731340)', 'ADI'),
('AMER MEDICAL RESPONSE(POB 749667)', 'AMERICAN MEDICAL RESPONSE WEST');

-- QUERY
SELECT
CASE
when RemitVendor.ALIAS IS NULL THEN RemitVendor.NAME
when RemitVendor.ALIAS = '' THEN RemitVendor.NAME
else RemitVendor.ALIAS
END VENDOR_NAME
FROM @Temp RemitVendor;

-- OUTPUT
VENDOR_NAME
ABBOTT LABORATORIES INC
ABB OPTICAL GROUP
ACTION COMMUNICATIONS INC
ADI
AMERICAN MEDICAL RESPONSE WEST

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-03 : 01:45:12

short hand way is this


DECLARE @Temp TABLE (NAME VARCHAR(60),  ALIAS  VARCHAR(60));
INSERT INTO @Temp VALUES
('ABBOTT LABORATORIES INC (BOX 92679)', 'ABBOTT LABORATORIES INC'),
('ABB OPTICAL GROUP', ''),
('ACTION COMMUNICATIONS INC', NULL),
('ADI (POB 731340)', 'ADI'),
('AMER MEDICAL RESPONSE(POB 749667)', 'AMERICAN MEDICAL RESPONSE WEST');

SELECT COALESCE(NULLIF(ALIAS,''),NAME) FROM @Temp


output
-------------------------------
ABBOTT LABORATORIES INC
ABB OPTICAL GROUP
ACTION COMMUNICATIONS INC
ADI
AMERICAN MEDICAL RESPONSE WEST

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2013-08-05 : 14:51:35

Yay!! The coelesce worked!! Thank you very much. I will have to remember that trick for next time.

Have an awesome day!

Sherri

SLReid
Forum Newbie
Renton, WA USA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 00:02:38

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-06 : 15:58:36

You don't need anything even that convoluted:


CASE 
    WHEN RemitVendor.ALIAS > '' 
    THEN RemitVendor.ALIAS 
    ELSE RemitVendor.NAME 
END AS VENDOR_NAME

Edit: Added "AS VENDOR_NAME".

sivadss2007
Starting Member

18 Posts

Posted - 2013-08-28 : 10:34:46

SELECT
CASE
when RemitVendor.ALIAS IS NULL then RemitVendor.NAME
when RemitVendor.ALIAS = ' ' then RemitVendor.NAME
else RemitVendor.ALIAS
END VENDOR_NAME

P.Siva

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-29 : 05:15:26

Hi,
This may work...

SELECT ISNULL(Alias,Name) from @TEMP

P.Kameswara rao

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2013-08-29 : 13:28:03

Thanks all -- this is resolved. I used coelesce.

SLReid
Forum Newbie
Renton, WA USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources