CASE WHEN 2 Fields, 2 nulls

Posted on 2014-08-20
Last Modified: 2014-08-20
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
Question by:Southern_Gentleman
    LVL 65

    Expert Comment

    by:Jim Horn
    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').

       WHEN fp2.ConfirmDate IS NULL THEN
           CASE WHEN fp2.CommitDate IS NULL THEN
                  -- do both null here
                  -- do only ConfirmDate is null here
    ELSE fp2.ConfirmDate END
    LVL 45

    Accepted Solution

    Use coalesce.

      COALESCE (Date1, Date2, DefaultDate)

    SQL will accept the first non-null item in the list.
    LVL 21

    Expert Comment

    by:Randy Poole
    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
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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
    LVL 18

    Expert Comment

    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
    LVL 65

    Expert Comment

    by:Jim Horn
    >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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now