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)
 Need help with CASE query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swenri
Yak Posting Veteran

60 Posts
Posted - 11/28/2012 :  15:57:03  Show Profile  Reply with Quote
I need help with the CASE query please.

My output can not have a null or blank value. It has to have a value like ‘NVD’ or ‘N/A’ anything except NULL. For example :
(Select top 1
case when ISNULL (Neonate.Vacuum_Extraction ,'0' ) = 'true' then 'VC'
when ISNULL (Neonate.Forceps_Delivery,'0' )='true' then 'FD'
when ISNULL (Neonate.Assisted_Breech_Delivery,'0' )='true' then 'ABD'
when ISNULL (Neonate.Spontaneous_Breech_Delivery,'0')='true' then 'SBD'
when ISNULL (Neonate.Total_Breech_Extraction,'0')='true' then 'TBE'
when ISNULL (Neonate.Cesarean_Section,'0' )='true' then 'CS'
when ISNULL (Neonate.Normal_Vaginal_Delivery,'0' )='true' then 'NVD'
else 'N/A' end
from DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate as Neonate
where BLSession_Extended.sessionID = Neonate.sessionid ) as ALLVD,

This output will give me ‘NVD’, ‘N/A’ and NULL. This is necessary as my output should go to Crystal Reports which does not recognize a NULL value that I can work with. When I try and string fields together (concatenate) with a NULL value in the middle, it drops everything after the NULL field.

Can any body resolve the issue please. Appreciate all the help.

swenri
Yak Posting Veteran

60 Posts
Posted - 11/28/2012 :  16:57:31  Show Profile  Reply with Quote
Can one help please ?

I tried the below code which doesn't work.

I tried to resolve the issue by using your code. It doesn't work. Here is the sample data .. may be this will help to resolve the issue. Also I trie dto use SET NULLS atthe beginning of the code yet it doesn't work. Can anyone help me please ?

sessionid LastName FirstName ALLVD
0 Xxxx xxxx NULL
Instead of NULLS I need something like below
0 Xxxx xxxx N/A
0 zzz zzzz NVD


(Select top 1
case when Coalesce(Neonate.Vacuum_Extraction,'etc','N/A') = 'true' then 'VC'
when Coalesce(Neonate.Forceps_Delivery ,'etc','N/A') = 'true' then 'FD'
when Coalesce(Neonate.Assisted_Breech_Delivery ,'etc','N/A') = 'true' then 'ABD'
when Coalesce(Neonate.Spontaneous_Breech_Delivery ,'etc','N/A') = 'true' then 'SBD'
when Coalesce(Neonate.Total_Breech_Extraction ,'etc','N/A') = 'true' then 'TBE'
when Coalesce(Neonate.Cesarean_Section ,'etc','N/A') = 'true' then 'CS'
when Coalesce(Neonate.Normal_Vaginal_Delivery ,'etc','N/A') = 'true' then 'NVD'


else 'N/A' end
from DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate as Neonate
where BLSession_Extended.sessionID = Neonate.sessionid
) as ALLVD,

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 11/28/2012 :  17:03:01  Show Profile  Reply with Quote
My guess us that you need to COALESCE then entire sub query as there may not be a corrilation and that would result in a NULL value.

For example: 
DECLARE @FOo TABlE (ID INT, VAL VarCHAR(10))

INSERT @Foo
SELECT 1, NULL

DECLARE @BAR TABLE (ID INT)
INSERT @BAR
SELECT 1
UNION ALL SELECT 2

SELECT
ID,
(
    SELECT TOP 1 
    CASE 
        WHEN Val = 'true' THEN 'foo'
        ELSE 'N/A/' 
    END
    FROM @FOo AS F
    WHERE F.ID = B.ID
) AS Val
FROM @Bar AS B



SELECT
ID,
COALESCE((
    SELECT TOP 1 
    CASE 
        WHEN Val = 'true' THEN 'foo'
        ELSE 'N/A/' 
    END
    FROM @FOo AS F
    WHERE F.ID = B.ID
), 'No Val') AS Val
FROM @Bar AS B
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.05 seconds. Powered By: Snitz Forums 2000