using IF within SQL?

[toon10]

Hi

I have an SQL statement:

SQL = “SELECT DISTINCT
`Part No`, Description, `Invoice No`, `Planning Element`,
`Invoice Line`, `Invoice Date`, `Invoice Creator`,
`QTY Invoiced`, `Value (GBP)`, `Cost Of Sale`, `Currency Code`,
`Customer Group`, Region, `Sold to`, `Delivery Note`,
`Business Unit`, `Product Manager`, `Product Group`,
`Product Manager Code`, `Order Creator Name`,
`Customer Purchase Order`, Customer, `Country Code`,
`Sales Region`
FROM tblSales
WHERE (`Business Unit` = 'BA')
ORDER BY `Part No` ASC”

This returns some sales data.

I trigger this statement using within my VB code:
rsExecute.Open SQL, cn, adOpenStatic, adLockOptimistic, adCmdText

This works fine but I’ve been asked to provide an extra calculated margin field. The problem is that I need to put a condition on the statement as some of the output will result in a division by zero which causes an error in SQL execution. The resulting recordset is then set as the data in a datagrid.

I need to be able to say…

If `Value (GBP)` = 0 AND `Cost Of Sale` > 0 THEN
Margin = -100
ELSE IF `Value (GBP)` = 0 AND `Cost Of Sale` < 0 THEN
Margin = 0
ELSE
Margin = ((`Value (GBP)` - `Cost Of Sale`) / `Value (GBP)`) * 100
END IF

This would resolve any division by zero issues but how (if possible?) could I put this sort of condition within my SQL statement?

Thanks

 

[Sumitdev]

You could try using an iif statement:

iif(Value (GBP) = 0 AND Cost Of Sale > 0,
    -100, 
    iif(Value (GBP) = 0 AND Cost Of Sale < 0, 
        0, 
        (Value (GBP) - Cost Of Sale)/Value (GBP)) * 100
    )
) AS Margin

 

[toon10]

Thanks, that didn't quite work but it's given me somewhere to start. Apparently I can use case statements within SQL so I'm trying a variation on the following:

Margin = CASE WHEN `Value (GBP)` = 0 AND
`Cost Of Sale` > 0 THEN " - 100 " WHEN `Value (GBP)` = 0 AND
`Cost Of Sale` < 0 THEN " 0 " ELSE(`Value (GBP)` - `Cost Of Sale`)
/ `Value (GBP)`) * 100 END

It's not working either but I shall persist!

Thanks again

 

[HarleyQuinn]

What type of DB are you querying with your SQL?

If it is Access (as I am lead to believe by the "'s around the output) then I don't believe CASE statements work.

Can you tell us what isn't working (i.e. error messages) about the IIf and CASE statements?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[toon10]

Yeah, I'm using Access.

Unfortunately, the error message I get is very helpful (Unknown error)! I've tried to shorten the case statement just to get a start, it doesn;t like the = syntax

 

[Sumitdev]

Not sure about the case as I have never used it before! But could adding an additional iif to check if Value is 0 work?

iif(Value (GBP) = 0 AND Cost Of Sale > 0,
    -100, 
    iif(Value (GBP) = 0 AND Cost Of Sale < 0, 
        0, 
        iif(Value (GBP) = 0, 
            0, 
           ((Value (GBP) - Cost Of Sale)/Value (GBP)) * 100
        )  
    )
) AS Margin

 

[HarleyQuinn]

The CASE statement syntax would require you to drop the 'Margin =' and add 'as Margin' after your END.

Like I say though, I don't think CASE statements work with Access.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[toon10]

OK thanks guys, I'll give that a try

 

[ettienne]

Create the query in Access first, then copy the SQL statement from the query designer. You may need to replace " with ' in the SQL statement.