CODEWITHSUNDEEP
sqlsql-server-2012
erasmo carlos
erasmo carlos

Reputation: 682

Convert IIF to CASE (SQL Server 2012)

This is the second question I ask regarding a conversion from IIF to CASE.

The thing that confuses me from the IIF is when one of the conditions becomes a second IIF. When that happens in the IIF code, I have trouble understanding whether I need just to put it as another WHEN, or if I need to start an nested CASE.

This is the IIF statement that needs conversion.

IIF(([FirstPublicationDate] IS NULL), IIF(([PublicationDate] IS NULL), GETDATE(), [PublicationDate]), [FirstPublicationDate])

Thank you for your help.

Upvotes: 2

Views: 1169

Answers (3)

Code Different
Code Different

Reputation: 93181

Make a tree diagram: 

                                                --- GETDATE() 
                                        (null) /
                                              /
                        --- PublicationDate - 
                (null) /                      \
                      /                        \
FirstPublicationDate -                          --- PublicationDate
                      \
                       \
                        --- FirstPublicationDate

It's clear that the IIF statements wants to return FirstPublicationDate if it's not null; then PublicationDate and finally GETDATE(). You can shorten it to a single, much more readable COALESCE statement:

SELECT COALESCE(FirstPublicationDate, PublicationDate, GETDATE())

Upvotes: 2

Saic Siquot
Saic Siquot

Reputation: 6513

This is the not-nested equivalent of your IIFs, which is more redable

SELECT CASE 
  WHEN FirstPublicationDate IS NOT NULL THEN FirstPublicationDate
  WHEN PublicationDate      IS NOT NULL THEN PublicationDate 
  ELSE GETDATE() 
END

But even on most sqls you have for this case a powerful and simplest option:

SELECT COALESCE(FirstPublicationDate, PublicationDate, GETDATE())

You can read about coalesce here, includes a comparison to case

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

The same IIF can be written with nested CASE statement as:

SELECT CASE WHEN [FirstPublicationDate] IS NULL THEN
         CASE WHEN [PublicationDate] IS NULL THEN GETDATE() ELSE [PublicationDate] END
       ELSE [FirstPublicationDate] END

Upvotes: 0

Related Questions