Home > IT Answers > AS/400 > How to view unique entry of record in collum...
Shreekant
40 pts.
 How to view unique entry of record in collumn?
AS/400 Query, AS/400 database, 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' ?
ASKED: Sep 7, 2010  9:11 AM GMT
UPDATED: September 9, 2010  2:04:13 PM GMT
RELATED QUESTIONS
DanTheDane
2,540 pts.
 
  Help
 Approved Answer - Chosen by EmNichs
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:

 Line   ....+....1
        COL1  COL2
 000001 AAA   aaa 
 000002           
 000003 BBB   bbb 
 000004           
 000005 CCC   bbb 
 000006           
 000007 CCC   ccc

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:

 Line   ....+....1
        COL1  COL2
 000001 AAA   aaa
 000002 BBB   bbb
 000003 CCC   bbb
 000004 CCC   ccc

DanF
ANSWERED:  Sep 8, 2010  9:27 PM (GMT)  by DanTheDane   2,540 pts.
 
Other Answers:
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:

 Line   ....+....1
        COL1  COL2
 000001 AAA   aaa 
 000002           
 000003 BBB   bbb 
 000004           
 000005 CCC   bbb 
 000006           
 000007 CCC   ccc

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:

 Line   ....+....1
        COL1  COL2
 000001 AAA   aaa
 000002 BBB   bbb
 000003 CCC   bbb
 000004 CCC   ccc

DanF
Last Wiki Answer Submitted:  Sep 8, 2010  9:27 PM (GMT)  by  DanTheDane   2,540 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

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

TomLiotta
 66,955 pts.
 

If this doesn’t make the OP provide more details, nothing will.

Carlosdl
 60,255 pts.
 

…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

TomLiotta
 66,955 pts.
 

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…

Shreekant
 40 pts.
 

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

TomLiotta
 66,955 pts.
 

BTW, this would be much simpler with a basic SQL SELECT DISTINCT… query. 

SELECT DISTINCT Col1, Col2 FROM mytable

Tom

TomLiotta
 66,955 pts.
 

Thanks Dan, It works

Shreekant
 40 pts.