June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.
Course Of The Month
5 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
AS/400 Query
Posted on 2004-04-20
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.
Thanks you.
[X]
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
5 Comments
Active 4 days ago
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.
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.
Active 1 day ago
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
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
Active 1 day ago
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
After seeing many questions for JRNL_WRAP_ERROR for replication failure, I thought it would be useful to write this article.
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
Suggested Courses
Course of the Month5 days, 3 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.