SQL Statement needs to use if and else

I am trying to write what is seemingly a very simple statement that will retrieve 2 columns
1 part_no
2 qty_on_hand
However I need qty on hand to say if qty_on_hand =>30, 30, else qty_on_hand

the results should look like
1234.12345 30
1235.13265 5

SELECT item_no,
FROM MYTABLE

sadly above is all I have so far.
Jeremy KirkbrideAsked:
Who is Participating?
 
DultonConnect With a Mentor Commented:
select item_no, case when qty_on_hand >= 30 THEN 30 ELSE qty_on_hand END as [Qty_On_hand]
from MyTable
0
 
Bill BachPresidentCommented:
You list a couple of SQL engines in the TOPICS list, so it is unclear which you are using.  You may also be able to use the IF statement:

select item_no, IF(qty_on_hand >= 30,30,qty_on_hand) from MyTable
0
 
PortletPaulfreelancerCommented:
Although Bill is likely to disagree with me I would suggest you stick with CASE expressions for SQL queries.

It is true that MySQL allows the syntax that Bill has provided, and a similar syntax is permitted in SQL Server 2012 like this:

select item_no, IIF(qty_on_hand >= 30,30,qty_on_hand) from MyTable

but both dbms types, and all versions I'm familiar with, support the CASE expression provided in the first answer.

No points please.
0
 
Jeremy KirkbrideAuthor Commented:
Thanks so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.