How to view unique entry of record in collumn?

40 pts.
Tags:
AS/400 database
AS/400 Query
AS/400 Reports
I have table containing same multiple records in collumn, when I goes into 'Define Query' and 'Select Records' of 'Query Definition Option', it shows me all record list, but I like to view only a single entry of each record. How could be querry in 'Select Records' of 'Query Definition Option' or do I need to make any another changes in 'Define the Query' ?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Shreekant,

All of your quoted headings can be found on the first panel shown when using the WRKQRY command (at least with V6R1 which I use).
To me it is therefor very safe to base an answer to your qustion on Query/400.

I created your testfile, and did the trick by using these options of the Query/400 (WRKQRY).

a. “Specify file selections”
(comments not nescessary..)

b. “Select sort fields”
- ‘Sort Prty’ ’1′ for COL1 and ’2′ for COL2 (leave the ‘A’s (=ascending).

c. “Define report breaks”
- Enter ’1′ in the ‘Break Level’ column for both COL1 and COL2; leave the defaults on the next 2 panels (‘Format Report Break’)

d. “Select output type and output form”
- Enter ’2′ in the ‘Form of output’ field indicating ‘summary only’; – else leave defaults.

Result is:
<pre>
Line ….+….1
COL1 COL2
000001 AAA aaa
000002
000003 BBB bbb
000004
000005 CCC bbb
000006
000007 CCC ccc
</pre>
To avoid the blank lines in the report (line 2, 4 and 6, you may change the ‘Output type’ of the “Select Output Type and Output Form” to ’3′ and enter a filename and
library for the result of the query. This will produce a file with only line 1, 3, 5 and 7 (see above); – run q new simple query over this file and – voila – there you have the wanted result:
<pre>
Line ….+….1
COL1 COL2
000001 AAA aaa
000002 BBB bbb
000003 CCC bbb
000004 CCC ccc
</pre>
DanF

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Here is a sample table that has three columns -- Col1, Col2 and Col3. Columns Col1 and Col3 are duplicated in multiple rows. The Col2 column has a numeric value that can be different for each row.
    Col1  Col2  Col3
    ----  ----  ----
    ABC     12  xyz
    ABC     32  xyz
    ABC     43  xyz
    DEF     12  uvw
    DEF     32  uvw
    DEF     65  uvw
    GHI     12  rst
    GHI     76  rst
    GHI    115  rst
    Please show us how you would want your query to display those rows. If my example does not fit your requirement, then please provide an example that would work and show us how the query should report that example. Thank you. Tom
    125,585 pointsBadges:
    report
  • carlosdl
    If this doesn't make the OP provide more details, nothing will.
    69,920 pointsBadges:
    report
  • TomLiotta
    ...nothing will. We'll find out, eh? I work at eliciting details, but it's not a particularly fruitful effort. Every once in a while, though... Tom
    125,585 pointsBadges:
    report
  • Shreekant
    Hi, as sample I have 2 columns in a table, if records are display's in such way, Col1 Col2 AAA aaa AAA aaa BBB bbb CCC bbb CCC ccc AAA aaa BBB bbb Like to view data in such format Col1 Col2 AAA aaa BBB bbb CCC bbb CCC ccc like to find out same...
    40 pointsBadges:
    report
  • TomLiotta
    You almost certainly want a Summary Query, but I can't determine what product you are using. Neither the Query for iSeries nor the Query Manager products have a menu option nor screen labeled 'Query Definition Option' that I can see. You would set both Col1 and Col2 as 'Break' columns and choose the summary output type. What version of i5/OS are you running? How do you get into the query function? (If you know the name of the query product, that will help.) I might be better able to connect to a configuration that matches yours if I know what to work with. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    BTW, this would be much simpler with a basic SQL SELECT DISTINCT... query.
    SELECT DISTINCT Col1, Col2 FROM mytable
    Tom
    125,585 pointsBadges:
    report
  • Shreekant
    Thanks Dan, It works
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following