Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
AS/400 Query
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.
Asked:
1 Solution
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
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
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.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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.