Creating a view with equivalent to Excel if and function 1

[GraemeBerry]

I have a table containing details of order_qty, despatch_qty and invoice_qty, we call this table salesorders.

I need to return an 'order status' field narrative based upon nested if and statements but cannot get this to work in SQL - I belive the CASE funtion should do the trick but have had no luck.

In short the narrative to be returned would be;

If order_qty > 0 and despatch_qty = 0 and invoice_qty = 0 then narrative "Order Entered"
Else
If order_qty > 0 and despatch_qty = order_qty and invoice_qty = 0 then narrative "Order Fully Despatched"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty = 0 then narrative "Order Partially Despatched Despatched"
Else
If order_qty > 0 and despatch_qty =order_qty and invoice_qty = order_qty then narrative "Invoiced"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty = despatch_qty then narrative "Order Partially Despatched But Despatches Fully Invoiced"
Else
If order_qty > 0 and despatch_qty >0 and despatch_qty < order_qty and invoice_qty >0 and invoice_qty < despatch_qty then narrative "Order Partially Despatched and Despatches Partially Invoiced"
Else "Unknown Status"

Any suggestions gratefully received

 

[gmmastros]

Try this....

Select Case When order_qty > 0 and despatch_qty = 0 and invoice_qty = 0 
            then "Order Entered"
            When order_qty > 0 and despatch_qty = order_qty and invoice_qty = 0 
            then "Order Fully Despatched"
            When order_qty > 0 and despatch_qty >0 and  despatch_qty < order_qty and invoice_qty = 0 
            then "Order Partially Despatched Despatched"
            When order_qty > 0 and despatch_qty =order_qty and invoice_qty = order_qty 
            then "Invoiced"
            When order_qty > 0 and despatch_qty >0 and  despatch_qty < order_qty and invoice_qty = despatch_qty 
            then "Order Partially Despatched But Despatches Fully Invoiced"
            When order_qty > 0 and despatch_qty >0 and  despatch_qty < order_qty and invoice_qty >0 and invoice_qty < despatch_qty 
            then "Order Partially Despatched and Despatches Partially Invoiced"
            Else "Unknown Status"
            End As Narrative
From   YourTable

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Don't forget to change your double quotes to sigles.

Simi