SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case statement not behaving correctly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 08/02/2013 :  20:33:52  Show Profile  Reply with Quote
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

547 Posts

Posted - 08/02/2013 :  20:46:22  Show Profile  Reply with Quote


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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/03/2013 :  01:45:12  Show Profile  Reply with Quote
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
Go to Top of Page

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 08/05/2013 :  14:51:35  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/06/2013 :  00:02:38  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
336 Posts

Posted - 08/06/2013 :  15:58:36  Show Profile  Reply with Quote
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".

Edited by - ScottPletcher on 08/28/2013 11:21:42
Go to Top of Page

sivadss2007
Starting Member

India
18 Posts

Posted - 08/28/2013 :  10:34:46  Show Profile  Reply with Quote
SELECT
CASE
when RemitVendor.ALIAS IS NULL then RemitVendor.NAME
when RemitVendor.ALIAS = ' ' then RemitVendor.NAME
else RemitVendor.ALIAS
END VENDOR_NAME

P.Siva
Go to Top of Page

kameswararao polireddy
Starting Member

India
19 Posts

Posted - 08/29/2013 :  05:15:26  Show Profile  Reply with Quote
Hi,
This may work...

SELECT ISNULL(Alias,Name) from @TEMP

P.Kameswara rao
Go to Top of Page

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 08/29/2013 :  13:28:03  Show Profile  Reply with Quote
Thanks all -- this is resolved. I used coelesce.

SLReid
Forum Newbie
Renton, WA USA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000