how to use case with these 2 conditions null and doesnt exist?

Question

how to use case with these 2 conditions null and doesnt exist?

montserrat.deza

Right there with Babe

Points: 725
More actions
June 23, 2017 at 12:34 pm

#324238

Hello experts,
Im having difficulty to write these query with both options, these is what I have so far and is working in a SQL query:
case when sola.ECCN is null then icd.ECCN else sola.ECCN end AS TESTVALUEECCN
but how can I add the doesn't exist option??
I need to show this value icd.ECCN when this value sola.ECCN is null or doesnt exist.
Any help will be much much appreciate it

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

montserrat.deza - Friday, June 23, 2017 12:34 PM
Hello experts,
Im having difficulty to write these query with both options, these is what I have so far and is working in a SQL query:
case when sola.ECCN is null then icd.ECCN else sola.ECCN end AS TESTVALUEECCN
but how can I add the doesn't exist option??
I need to show this value icd.ECCN when this value sola.ECCN is null or doesnt exist.
Any help will be much much appreciate it

It looks like you can replace your CASE statement with:

ISNULL(sola.ECCN, icd.ECCN) TestValueCCN

From your description of sola.ECCN not existing, I'm guessing you're OUTER JOINing sola to icd. This is only a guess. In that case, if the row in sola doesn't exist, it'll return NULL in the result set, so the NULL check (either CASE or ISNULL) will handle it.

If my guess is wrong, please post DDL and your full query so I can better see what you're working with.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323518 More actions · Answer 2

can't you just add another WHEN THEN?
case when icd.ECCN is not null then icd.ECCN when sola.ECCN is not null then sola.ECCN else 'missing' end AS TESTVALUEECCN

that could also be written with a COALESCE, whihc is easier to read, but is still converted to the exact same case
COALESCE(icd.ECCN,sola.ECCN,'missing')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

montserrat.deza Right there with Babe Points: 725 More actions · Answer 3

Thank you!!! that is easy lol!!! Thank you so much will work...