Geek Answers Handbook

Sql-server, IF statement in SQL Query

I am having problems using the instructions IFin this sql server request.

What I want:

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

Is there a way to do this using the instructions IFin the request?

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_)
+5
source share
5 answers

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

+9
source

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:

And the COALESCE and ISNULL answers received in other answers are equally true for this particular case (and easier, frankly)

+11

CASE:

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

COALESCE ISNULL:

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

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

, "AS DeliveryName" IF?

, MySQL...

+1

More articles:


All Articles