Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

AS/400 Query

Posted on 2004-04-20
5
1,627 Views
Last Modified: 2008-02-01
Is it possible to do any type of "If/Then" statement in Query.  I need to filter out some negative number results without filtering out the whole record with a statement saying that if the field is less than zero, move zero to a new field and then have that new field displayed on the report.

Thanks you.
0
Comment
Question by:pseils
5 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 10873230
No not directly in 1 step, what you can do is create an Query that select all your records with "good" data and send them to a temp file.
in step 2 you take the dame query and filter the "Bad"records and append them to the same file (without the unwanted field(s)). In the temp file these fields will remain blank or zero.
Now you can generate the report.


0
 
LVL 33

Accepted Solution

by:
shalomc earned 125 total points
ID: 10875457
If you use Query Management Queries rather than Query400, then it is possible.

In QMQRY, you can embed SQL in the query object, and use SQL case, like this:

table description
-------------------
item char 15
onhand dec 15,5

select item, case when onhand<0 then 0 else onhand end as onhand from inv_file


ShalomC
0
 
LVL 27

Expert Comment

by:tliotta
ID: 10884165
pseils:

If you mean the Query/400 product, the easiest way is to create an SQL VIEW that presents the data in the way you need it, then write your query over that view.

But this really isn't a task for Query/400. As mentioned by ShalomC, a Query Management query is better suited. One potential issue is that you don't have the SQL Development Kit product. That would make it harder.

Fortunately, you could still create and use QM queries; you'd just need to learn the details of do-it-yourself.

Tom
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you are looking at this article, you have most likely been hit by some version of ransomware and are trying to find out if there is anything you can do, or what way you should react - READ ON!
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 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