Avatar of erica686
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.

Microsoft DevelopmentMicrosoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
erica686

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
erica686

Thanks!!
mofoworking

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)

Open in new window

ASKER
erica686

Thankyou to both- I didn't realise at first that the first solution didn't effect the where clause. thanks!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes