• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

CASE WHEN 2 Fields, 2 nulls

Probably an easy question but have a short deadline. I have two fields each with the a possibility of NULLS. I already did a case when but now realized that I may still have NULL values. How would I get rid of the final NULL if both fields are null. is it possible to nest inside an already created CASE WHEN?  
Here is my current Case When.

CASE WHEN fp2.ConfirmDate IS NULL THEN fp2.CommitDate ELSE fp2.ConfirmDate END
0
Southern_Gentleman
Asked:
Southern_Gentleman
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us a data mockup of what you're trying to pull off here.

>it possible to nest inside an already created CASE WHEN?  
Yes, here's an example (scroll half way down to 'CASE blocks can also be nested within themselves').

CASE
   WHEN fp2.ConfirmDate IS NULL THEN
       CASE WHEN fp2.CommitDate IS NULL THEN
              -- do both null here
       ELSE
              -- do only ConfirmDate is null here
       END
ELSE fp2.ConfirmDate END
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Use coalesce.

  COALESCE (Date1, Date2, DefaultDate)

SQL will accept the first non-null item in the list.
0
 
Randy PooleCommented:
Select isNull(fp2.ConfirmDate,isNull(CommitDate,'2014-01-01')) 

Open in new window


 just replace 2014-01-01 with what you want is to be if both are null
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Aneesh RetnakaranDatabase AdministratorCommented:
nesting will work

CASE WHEN fp2.ConfirmDate IS NULL THEN fp2.CommitDate ELSE  
        case when fp2.ConfirmDate  is null then '' else  fp2.CommitDate  end END
0
 
sventhanCommented:
Here is how it works ...

Select Case NULL < === your fp2.confirmdate
  When NULL then 0 // Checks for NULL = NULL
  else 1 // NULL = NULL is not true end
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>How would I get rid of the final NULL if both fields are null.
Next time please do a better job in explaining your question, preferably with a data mockup of both before and after, so we don't have multiple experts assuming a lot of facts and providing different solutions.  Thanks in advance.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now