RandomScrawls
Decidedly mundane…

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.

Steve Canter on SystemiNetwork Forum

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.

Sample Physical File with Duplicate Entries in One of the Fields

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 WRKQRY.

  1. Do the usual file selections process. Yadda yadda yadda.
  2. Select the field you want to distinctly select/summarize. In this case, it’s SCCODE. 001002
  3. 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.02030405
  4. Select Output Type, choose Summary Only.1011
  5. See the results. The result should be similar with the faithful SELECT DISTINCT clause, 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.20

4 Responses to “How to Mimic SQL’s SELECT DISTINCT Clause with IBM AS/400’s WRKQRY”

  1. One of my boss did just explain me the same thing:
    [[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]]

    No wonder, it didn´t work.

    Yous step / step did hit the nail!, Thanks!!

  2. And again.

    Thanks!, Thanks. A lot ! a Thousand of thanks!

  3. mafri…

    [...]How to Mimic SQL’s SELECT DISTINCT Clause with IBM AS/400′s WRKQRY « RandomScrawls[...]…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: