sql-server, IF statement within SQL Query

Question

I'm having trouble with using an IF statement within this sql-server query.

What I want:

If salesHeader.[Ship-to Name] isn't null, I need to return that AS DeliveryName, if it is null, return something else.

Is there a way to do this with an if statement in a query?

SELECT
   poHeader.No_ AS PONumber, 
   poHeader.[Buy-from Vendor No_] AS VendorNumber, 
   poHeader.[Document Date] AS DocDate, 
   salesHeader.GiftMessage, 
   salesHeader.[Delivery Comment] AS DeliveryComment,                     
   salesHeader.[Shipment Date] AS DeliveryDate, 
   IF salesHeader.[Ship-to Name] IS NOT NULL 
      salesHeader.[Ship-to Name] AS DeliveryName
   ELSE
      poHeader.[Ship-to Name] AS DeliveryName
   END 
FROM         
    dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
LEFT OUTER JOIN
    dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 
INNER JOIN
    dbo.[Crocus Live$Vendor] AS vendor ON poHeader.[Buy-from Vendor No_] = vendor.No_ 
LEFT OUTER JOIN
    dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = dbo.fnGetSalesOrderNumber(poHeader.No_)

gbn · Accepted Answer · 2009-11-23 16:28:05Z

11

Use CASE, not IF

CASE 
    WHEN salesHeader.[Ship-to Name] IS NOT NULL THEN salesHeader.[Ship-to Name]
    ELSE poHeader.[Ship-to Name]
END AS DeliveryName

Edit:

Both COALESCE and ISNULL answers give in other answers are equally valid too for this particular case (and simpler, frankly)

edited Nov 23, 2009 at 16:28

answered Nov 23, 2009 at 15:24

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2009-11-23 16:45:43Z

10

Or use COALESCE...

SELECT 
COALESCE(salesHeader.[Ship-to Name], poHeader.[Ship-to Name]) AS DeliveryName

COALESCE will return the first non-NULL value in the list

edited Nov 23, 2009 at 16:45

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 23, 2009 at 15:32

lJohnson

3552 silver badges11 bronze badges

Comments

Tomek Szpakowicz · Accepted Answer · 2009-11-23 16:04:53Z

5

You can use CASE:

(CASE WHEN cond-1 THEN value-if-1
      WHEN cond-2 THEN value-if-2
      ELSE default-value END) AS column-name

Or in this situation you can use COALESCE or ISNULL:

COALESCE(salesHeader.[Ship-to Name], poHeader.[Ship-to Name]) AS DeliveryName

ISNULL(salesHeader.[Ship-to Name], poHeader.[Ship-to Name]) AS DeliveryName

edited Nov 23, 2009 at 16:04

answered Nov 23, 2009 at 15:38

Tomek Szpakowicz

14.6k3 gold badges37 silver badges57 bronze badges

Comments

priyanka.sarkar · Accepted Answer · 2009-11-23 15:41:41Z

2

As what gbn said, use CASE statement instead.

Since he has already done the coding, so I don't want to place the same thing again.

Instead just giving you some site to refer for your understanding

a) SQL SERVER – CASE Statement/Expression Examples and Explanation

b) CASE function in SQL Server 2005 - part I

c) CASE function in SQL Server 2005 - part II

d) CASE Function in SQL Server 2005 - part III

answered Nov 23, 2009 at 15:41

priyanka.sarkar

26.7k43 gold badges133 silver badges182 bronze badges

Comments

Seb · Accepted Answer · 2009-11-23 15:27:15Z

1

Not sure, but don't you have to put the 'AS DeliveryName' outside the IF?

At least that's how it works in MySQL...

answered Nov 23, 2009 at 15:27

Seb

25.2k6 gold badges69 silver badges85 bronze badges

Collectives™ on Stack Overflow

sql-server, IF statement within SQL Query

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related