'If' in select statement.

[stephenk1973]

I am try completeing a calculation in the select part of the statement. If the amount transported against an order is greater than the order i would like to show 0 (i.e. i don't want o show a negative value). In Access my code would be something like .....

IIF(tblOrder.Q6=<tblTrans.Q6,0,tblOrder.Q6-tblTrans.Q6) AS OutstandingAmt

Unfortunately this doesn't translate across, how do i do this strSQL

Thanks

Stephen

 

[r937]

case when tblOrder.Q6 <= tblTrans.Q6 
     then 0 
     else tblOrder.Q6-tblTrans.Q6 
 end AS OutstandingAmt

r937.com | rudy.ca

 

[hughesai]

I've also been looking for a replacement for the Access IIf statement in SQL Server.

I understand the case statement and it's usage, but it is not supported by the Query Designer in Enterprise Manager (SQL-2000) so for me it's usage is limited to relatively simple queries.

Is there any way of getting this sort of result using the Query Designer. My best attempt so far is to create a user defined function. Is this the best solution, or is there a more practical solution for one-off calculations.

 

[stephenk1973]

Took me a bit of getting used to but got there eventually, though not as friendly in the designed i now find it more flexible and easier to decypher when looking back. General gist as i work it....

IIF(FIELD='Field value',[TRUE VALUE],[FALSE VALUE])

Case FIELD when 'Field value' then [TRUE VALUE],[FALSE VALUE]

Haven't really tried nesting yet but sure it is possible. If your having trouble i'd start a new thread quoting your current code and what your aim is.

Good luck