erica686
asked on
How do I create an 'IF' function in a query rather than as an expression??
I have created the following query:
SELECT MATTER_NO, BALANCE, WIP_TOLERANCE
FROM MATTER
WHERE (BALANCE>WIP_TOLERANCE)
I need the WIP_TOLERANCE to return '80' whenever the value is '0'. Whenever the value is not zero I want it to return that value. (EG if WIP_TOLERANCE is 5, return 5, if WIP_TOLERANCE is 0, always return 80)
I know how to do this by creating an expression using the IIF function, however I need this to be written into the query as I also need it to be a part of the WHERE clause. (Ie WHERE BALANCE>WIP_TOLERANCE - I never want to be <0)
Any help asap would be fantastic!!!
Erica.
SELECT MATTER_NO, BALANCE, WIP_TOLERANCE
FROM MATTER
WHERE (BALANCE>WIP_TOLERANCE)
I need the WIP_TOLERANCE to return '80' whenever the value is '0'. Whenever the value is not zero I want it to return that value. (EG if WIP_TOLERANCE is 5, return 5, if WIP_TOLERANCE is 0, always return 80)
I know how to do this by creating an expression using the IIF function, however I need this to be written into the query as I also need it to be a part of the WHERE clause. (Ie WHERE BALANCE>WIP_TOLERANCE - I never want to be <0)
Any help asap would be fantastic!!!
Erica.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dont forget the WHERE statement
SELECT MATTER_NO, BALANCE, CASE WHEN WIP_TOLERANCE = 0 THEN 80 ELSE WIP_TOLERANCE END AS WIP_TOLERANCE
FROM MATTER
WHERE (BALANCE > CASE WHEN WIP_TOLERANCE = 0 THEN 80 ELSE WIP_TOLERANCE END)
ASKER
Thankyou to both- I didn't realise at first that the first solution didn't effect the where clause. thanks!
ASKER