SQL if function

Question

I have a view in SQL and I need to get an if function in there, below is my statement.

    SELECT  TOP (100) PERCENT 
    dbo.SorDetail.MLineShipDate,
    dbo.SorMaster.SalesOrder, 
    dbo.SorDetail.SalesOrderLine, 
    dbo.SorMaster.CustomerPoNumber, 
    dbo.SorMaster.Customer, 
    dbo.ArCustomer.Name AS CustomerName, 
    dbo.SorMaster.Salesperson, 
    dbo.InvMaster.StockCode, 
    dbo.InvMaster.Description, 
    dbo.InvMaster.ProductClass, 
    dbo.ShortOrderOrigOrderQty.OrderUom, 
    dbo.ShortOrderOrigOrderQty.OrderQty AS OrderQtyOrig, 
    dbo.ShortOrderOrigOrderQty.LineValue AS LineValueOrig,     
    ISNULL(dbo.ShortOrderTrimQty.TrimQty, 0) AS OrderQtyTrim, 
    dbo.SorDetail.MPrice * ISNULL(dbo.ShortOrderTrimQty.TrimQty, 0) AS LineValueTrim,
    dbo.SorDetail.MOrderQty AS OrderQtyCurrent, 
    dbo.SorDetail.MPrice * dbo.SorDetail.MOrderQty AS LineValueCurrent,      
    dbo.ShortInvoiced.QtyInvoiced AS OrderQtyPicked, 
    ISNULL(dbo.ShortOrderPicked.NetSalesValue, 0) AS LineValuePicked, 
    ISNULL(dbo.ShortInvoiced.QtyInvoiced, 0)  / dbo.ShortOrderOrigOrderQty.OrderQty * 100 AS [InFull%],
    ISNULL(dbo.ShortInvoiced.QtyInvoiced, 0) / dbo.SorDetail.MOrderQty * 100 AS [StockAvailability%],
    dbo.SorDetail.MWarehouse, 
    dbo.SorDetail.MStockDes, 
    dbo.SorMaster.OrderStatus, 
    dbo.SorMaster.InterWhSale
    FROM dbo.ShortOrderTrimQty 
    RIGHT OUTER JOIN dbo.SorMaster 
    INNER JOIN dbo.ShortInvoiced ON dbo.SorMaster.SalesOrder = dbo.ShortInvoiced.SalesOrder 
    RIGHT OUTER JOIN dbo.SorDetail ON dbo.ShortInvoiced.SalesOrderLine = dbo.SorDetail.SalesOrderLine 
      AND dbo.ShortInvoiced.SalesOrder = dbo.SorDetail.SalesOrder 
      AND dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder 
    LEFT OUTER JOIN dbo.ShortOrderPicked ON dbo.SorDetail.SalesOrderLine = dbo.ShortOrderPicked.SalesOrderLine 
    AND dbo.SorDetail.SalesOrder = dbo.ShortOrderPicked.SalesOrder 
  ON dbo.ShortOrderTrimQty.SalesOrderLine = dbo.SorDetail.SalesOrderInitLine 
   AND dbo.ShortOrderTrimQty.SalesOrder = dbo.SorDetail.SalesOrder 
    LEFT OUTER JOIN dbo.ShortOrderOrigOrderQty ON dbo.SorDetail.SalesOrderInitLine = dbo.ShortOrderOrigOrderQty.SalesOrderLine 
     AND dbo.SorDetail.SalesOrder = dbo.ShortOrderOrigOrderQty.SalesOrder 
    LEFT OUTER JOIN dbo.InvMaster ON dbo.SorDetail.MStockCode = dbo.InvMaster.StockCode 
    LEFT OUTER JOIN dbo.InvCustProdType ON dbo.SorDetail.MStockCode = dbo.InvCustProdType.KeyField 
    LEFT OUTER JOIN dbo.ArCustomer ON dbo.SorMaster.Customer = dbo.ArCustomer.Customer
    WHERE (dbo.SorDetail.LineType = '1') 
      AND (dbo.SorMaster.OrderStatus <> '*') 
      AND (dbo.SorMaster.DocumentType IN ('B', 'O')) 
      AND (dbo.SorMaster.Customer <> 'FAC002') 
      AND  (dbo.SorMaster.OrderStatus <> '\')
    ORDER BY dbo.SorMaster.SalesOrder, dbo.SorDetail.SalesOrderLine

Okay so I need the IF statement to do the following:

IF (IntWhSales = Y, then OrderQtyOrig = OrderQtyCurrent) else OrderQtyOrig

PLEASE help me

Possible duplicate of [How to perform an IF...THEN in an SQL SELECT?](http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select) — Heinzi, Dec 09 '16 at 12:24
You need this to do what? Return a different column or use a different filter? It's impossible to read this query. If you want to return different values, use the `CASE WHEN` statement, not `IF` — Panagiotis Kanavos, Dec 09 '16 at 12:27
In the future, you should format your code in such a way where we do not have to scroll left to right. — Neo, Dec 09 '16 at 12:41
Just so you're aware, the only purpose of an `ORDER BY` within a view is to complete the definition of other features (here, `TOP`). It does *not* guarantee in any way the order in which rows will be returned when this view is queried. And the optimizer is smart enough these days to know it doesn't need to do any sorting when it's asked for `TOP 100 PERCENT`. — Damien_The_Unbeliever, Dec 09 '16 at 12:53

Radu Gheorghiu · Answer 1 · 2016-12-09T12:39:09.387

The equivalent IF statement in SQL for your request is:

CASE 
    WHEN IntWhSales = 'Y'
        THEN OrderQtyCurrent
    ELSE OrderQtyOrig
END

You just plug this code into the SELECT part of your query.

SELECT TOP (100) PERCENT dbo.SorDetail.MLineShipDate
    , dbo.SorMaster.SalesOrder
    , dbo.SorDetail.SalesOrderLine
    , dbo.SorMaster.CustomerPoNumber
    , dbo.SorMaster.Customer
    ...
    , CASE 
        WHEN IntWhSales = 'Y'
            THEN OrderQtyCurrent
        ELSE OrderQtyOrig
    END                           AS [OrderQuantity]
FROM dbo.ShortOrderTrimQty
RIGHT OUTER JOIN dbo.SorMaster
...

you need to add the single quote to the Y sir. – Neo Dec 09 '16 at 12:38 — Neo, Dec 09 '16 at 12:38

score 0 · Answer 2 · answered Dec 09 '16 at 12:36

0

Add this to your select statement:

Case IntWhSales = 'Y' Then OrderQtyCurrent Else OrderQtyOrig End AS [OrderQuantity]

answered Dec 09 '16 at 12:36

Neo

3,309
7
35
44

SQL if function

2 Answers2