CODEWITHSUNDEEP
sql-serverif-statement
john
john

Reputation: 65

SQL Server if Statement >=

These are the questions:

Write a script that will give you six columns of output.

a) the customer first name
b) the customer last name
c) the order number
d) the item name
e) the discount
f) a column with the following words for the discount----either NONE, LESS THAN TWENTY, LESS THAN FORTY, or MORE THAN FORTY

I have done this:

SELECT 
    Customers.FirstName,
    Customers.LastName,
    Orders.OrderID,
    Products.ProductName,
    OrderItems.DiscountAmount,
    IF(OrderItems.DiscountAmount = 0, 'NONE', IF(OrderItems.DiscountAmount <= 20, 'LESS THAN TWENTY', IF(OrderItems.DiscountAmount <= 40, 'LESS THAN FORTY', 'MORE THAN FORTY'))) AS discount
FROM 
    Customers
INNER JOIN 
    Orders ON (Customers.ShippingAddressID = Orders.ShipAddressID 
           AND Customers.BillingAddressID = Orders.BillingAddressID)
INNER JOIN 
    OrderItems ON (Orders.OrderID = OrderItems.OrderID)
INNER JOIN 
    Products ON (OrderItems.ProductID = Products.ProductID)

It gives me an error on my if statement? Can you see what I'm doing wrong?

This is the database I'm using:

enter image description here

Error messages:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'IF'.

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

Upvotes: 0

Views: 102

Answers (3)

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

Use "case..when" statement here. Usage link here: case statement

Case 
    when OrderItems.DiscountAmount = 0 then 'NONE' 
    when OrderItems.DiscountAmount <= 20 then 'LESS THAN TWENTY' 
    when OrderItems.DiscountAmount <= 40 then 'LESS THAN FORTY' 
    else 'MORE THAN FORTY' 
End AS discount

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This is SQL Server. There is no if statement. Use case:

(CASE WHEN OrderItems.DiscountAmount = 0 THEN 'NONE'
      WHEN OrderItems.DiscountAmount <= 20 THEN 'LESS THAN TWENTY'
      WHEN OrderItems.DiscountAmount <= 40 THEN 'LESS THAN FORTY'
      ELSE 'MORE THAN FORTY'
 END) AS discount

Upvotes: 0

Niels Keurentjes
Niels Keurentjes

Reputation: 41968

Your database diagram and errors suggest you are using Microsoft SQL Server, not MySQL. The IF command/expression/keyword is not standardized and implemented differently in different platforms.

Therefore you should not use MySQL proprietary IF-syntax but the MSSQL proprietary IIF-syntax. IF is a control-of-flow statement in MSSQL.

Upvotes: 4

Related Questions