Need help with CASE query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with CASE query

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2012-11-28 : 15:57:03

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 - 2012-11-28 : 16:57:31

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,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-28 : 17:03:01

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources