Case statement troubles.

Question

Case statement troubles.

aldous.chris

SSC-Addicted

Points: 413
More actions
September 24, 2015 at 4:40 am

#399083

Hi All,
I am having trouble with a certain query - being the only SQL resource in my company, I do not have anyone to confirm or deny my logic...
I have three columns containing Id's (Account, Opportunity and Campaign). The requirement is to map all three columns to one based on certain logic.
The logic.
1. If Account, Campaign and Opportunity are not null, then Opportunity.
2. If Campaign and Account are not null, but Opportunity is null then Campaign.
3. If Opportunity and Account are not null, but Campaign is null then Opportunity.
4. If Opportunity and Campaign are not null, but Account is null, then Opportunity.
I have written a nested case statement in that order, with the final else statement being NULL. The problem which I am having is that the majority of the values are NULL in the column to be updated.
Do I need to specify all the above logic and is a nested case statement the best approach?
Thanks in advance for your help.
Cheers,
Chris

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

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

aldous.chris (9/24/2015)
Hi All,
I am having trouble with a certain query - being the only SQL resource in my company, I do not have anyone to confirm or deny my logic...
I have three columns containing Id's (Account, Opportunity and Campaign). The requirement is to map all three columns to one based on certain logic.
The logic.
1. If Account, Campaign and Opportunity are not null, then Opportunity.
2. If Campaign and Account are not null, but Opportunity is null then Campaign.
3. If Opportunity and Account are not null, but Campaign is null then Opportunity.
4. If Opportunity and Campaign are not null, but Account is null, then Opportunity.
I have written a nested case statement in that order, with the final else statement being NULL. The problem which I am having is that the majority of the values are NULL in the column to be updated.
Do I need to specify all the above logic and is a nested case statement the best approach?
Thanks in advance for your help.
Cheers,
Chris

Try this:

coalesce(Opportunity,Campaign,Account)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Ken McKelvey SSCoach Points: 18765 More actions · Answer 2

It looks as though you have precedence:

CASE

WHEN Opportunity IS NOT NULL THEN Opportunity

WHEN Campaign IS NOT NULL THEN Campaign

ELSE Account

END

Edit: Did not notice Phil's reply. His is probably better.

WayneS SSC Guru Points: 95409 More actions · Answer 3

Phil Parkin (9/24/2015)
aldous.chris (9/24/2015)
Hi All,
I am having trouble with a certain query - being the only SQL resource in my company, I do not have anyone to confirm or deny my logic...
I have three columns containing Id's (Account, Opportunity and Campaign). The requirement is to map all three columns to one based on certain logic.
The logic.
1. If Account, Campaign and Opportunity are not null, then Opportunity.
2. If Campaign and Account are not null, but Opportunity is null then Campaign.
3. If Opportunity and Account are not null, but Campaign is null then Opportunity.
4. If Opportunity and Campaign are not null, but Account is null, then Opportunity.
I have written a nested case statement in that order, with the final else statement being NULL. The problem which I am having is that the majority of the values are NULL in the column to be updated.
Do I need to specify all the above logic and is a nested case statement the best approach?
Thanks in advance for your help.
Cheers,
Chris
Try this:
coalesce(Opportunity,Campaign,Account)

I agree with this for Opportunity. For Campaign, it's only supposed to be selected if Opportunity is null and both Campaign and Account is not null... using this logic, Campaign will be selected if Opportunity is null, Campaign is not null, regardless of whether Account is null or not.

Additionally, I'm not sure that Account should ever be a possibility for the result - that is never specified to be returned in the requirements.

Considering all of this, I think the solution is:

DECLARE @test-2 TABLE (

Opportunity INTEGER,

Campaign INTEGER,

Account INTEGER);

INSERT INTO @test-2

VALUES (1, 2, 3), -- all not null - use Opportunity

(1, NULL, 3), -- Opportunity & Account not null, Campaign is null - Use Opportunity

(1, 2, NULL), -- Opportunity & Campaign not null, Account is null - Use Opportunity

(NULL, 2, 3), -- Opportunity is null, Campaign and Account are not null - use Campaign

(NULL, NULL, 3), -- only Account is not null - return null - don't use any

(NULL, 2, NULL); -- only Campaign is not null - return null - don't use any

SELECT *

,CASE WHEN Opportunity IS NOT NULL THEN Opportunity

WHEN Campaign IS NOT NULL AND Account IS NOT NULL THEN Campaign

ELSE NULL

END

FROM @test-2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2