How to Mimic SQL’s SELECT DISTINCT Clause with IBM AS/400’s WRKQRY
Several days ago, I was stuck with this problem: how do I do a version of SQL’s
SELECT DISTINCT clause with IBM AS/400’s (iSeries)
WRKQRY command. In other words, how to summarize a field. You see, my user ID on the system is restricted to a menu – not even given the command-line, in fact – which is devoid of the ability to run the
STRSQL command. After a moment of head-banging, hair-pulling and frantic google searching, here’s the solution.
Define a report break containing [the field you want to distinctly select]. Then in Select Output Type, choose Summary Only. You will only see one occurrance [sic] for each distinct break level. The one downside to this is that Query/400 will insert a blank line in between each row. If you want to print out the data without the blank lines, you can run the output of this query to a temporary file and then query the temporary file seperately.
Thank you, Steve, for that verbose – but useful – explanation. Now, if you let me supplement your explanation with these two screenshots, all will be better, I think.
So, let’s say I have this physical file a.k.a. table.
As you can see, the file has two fields, Deposit Type Code (
SCCODE) and Type Description (
PSCDES). Now, let’s pretend I want to know all distinct values of the first field. With SQL, we’d use this query:
SELECT DISTINCT SCCODE FROM TABLE. This is how you do it with
- Do the usual file selections process. Yadda yadda yadda.
- Select the field you want to distinctly select/summarize. In this case, it’s SCCODE.
- Define a report break containing [the field you want to distinctly select]. A report break is basically the list of fields you put on your “
SELECT DISTINCT xxx, yyy” line. So, if you want to distinctively select two fields, you put two level of breaks here. The iSeries accepts up to six break levels for up to nine fields.
- Select Output Type, choose Summary Only.
- See the results. The result should be similar with the faithful
SELECT DISTINCTclause, albeit with one blank rows after each item. This can be worked around with saving the result to a database file and running another query and sorting the result field.