Solved

SQL Statement needs to use if and else

Posted on 2014-04-09
5
157 Views
Last Modified: 2014-04-10
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.
0
Comment
Question by:Jeremy Kirkbride
5 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 39990102
select item_no, case when qty_on_hand >= 30 THEN 30 ELSE qty_on_hand END as [Qty_On_hand]
from MyTable
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39990201
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39990543
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
 

Author Closing Comment

by:Jeremy Kirkbride
ID: 39991332
Thanks so much
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question