November 28, 2012 at 1:58 pm
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.
November 28, 2012 at 2:08 pm
What value(s) are able to escape the ELSE trap at the end of your CASE statement?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 28, 2012 at 2:08 pm
swenri (11/28/2012)
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.
I posted one response already and the more I look at this the more confused I am. Can you post ddl, sample data and desired output? Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 2:12 pm
Hello.
From what I'm seeing, you're usage of isnull will not work.
You are basically saying "when Neonate.Vacuum_Extraction is a null value, set it to string '0', and when string '0' equals string 'true' then set the value of the output column ALLVD to string 'VC' " which will never occur.
That subquery would only return 'N/A' because none of the when statements would resolve to true.
If only one of those fields would have a value, and the values are 'VC', 'FD',etc. you might try to use Coalesce(Neonate.Vacuum_Extraction, NeoNate.Forceps_Delivery, etc, 'N/A') which would return the first non-null value in the set.
As an aside, if you need to get around the null concatenation issue, and are using 2008 or before, you can use: set concat_null_yields_null off. This is not advised as this functionality will be deprecated in SQL 2012.
Hope this helps!
swenri (11/28/2012)
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.
November 28, 2012 at 2:50 pm
Thank you for the help. 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 ?
sessionidLastName FirstName ALLVD
0Xxxxxxxx NULL
[font="Wingdings"]Instead of NULLS I need something like below[/font]
0Xxxxxxxx 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,
November 28, 2012 at 2:51 pm
Thank you for the help. 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 ?
sessionidLastName FirstName ALLVD
0Xxxxxxxx NULL
[font="Courier New"]Instead of NULLS I need something like below[/font]
0Xxxxxxxx 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,
November 28, 2012 at 3:00 pm
Shot in the dark as to getting you the data you want because I'm not sure I 100% understand but try this:
(Select top 1
Coalesce(Neonate.Vacuum_Extraction,Neonate.Forceps_Delivery,Neonate.Assisted_Breech_Delivery,Neonate.Spontaneous_Breech_Delivery,Neonate.Total_Breech_Extraction,Neonate.Cesarean_Section,Neonate.Normal_Vaginal_Delivery,'N/A')
from DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate as Neonate
where BLSession_Extended.sessionID = Neonate.sessionid
) as ALLVD
Although trying to read between the lines I assume that the fields you are referencing are bit flags to designate the type of birth, not the actual codes, in which case you want to find the first non-null value of those and present a specific value for each, so give this a try.
(Select top 1
Coalesce(
case
when Neonate.Vacuum_Extraction is not null then 'VC'
end
,
case
when Neonate.Forceps_Delivery is not null then 'FD'
end
,
case
when Neonate.Assisted_Breech_Delivery is not null then 'ABD'
end
,
case
when Neonate.Spontaneous_Breech_Delivery is not null then 'SBD'
end
,
case
when Neonate.Total_Breech_Extraction is not null then 'TBE'
end
,
case
when Neonate.Cesarean_Section is not null then 'CS'
end
,
case
when Neonate.Normal_Vaginal_Delivery is not null then 'NVD'
end
,'N/A')
from DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate as Neonate
where BLSession_Extended.sessionID = Neonate.sessionid
) as ALLVD
swenri (11/28/2012)
Thank you for the help. 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 ?sessionidLastName FirstName ALLVD
0Xxxxxxxx NULL
[font="Courier New"]Instead of NULLS I need something like below[/font]
0Xxxxxxxx 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,
November 28, 2012 at 3:05 pm
Yes we can help but you have to help us help you. You changed your ISNULL to COALLESCE but is seems that you still don't understand how it works.
case when Coalesce(Neonate.Vacuum_Extraction,'etc','N/A') = 'true' then 'VC'
This will only work when the value of Neonate.Vacuum_Extraction = 'true'. coalesce returns the first non null value in the list. N/A can NEVER be in the list because 'etc' is not null. The two hard coded values to not equal 'true'
As I said before. Please read the link in my signature about best practices when posting questions. If you would post ddl, sample data and desired output what you want is pretty simple. Or you can continue to bang your head against the wall with code that isn't going to work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply