Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swenri
Yak Posting Veteran

72 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

72 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

4614 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  
 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.02 seconds. Powered By: Snitz Forums 2000