Hi all,

I'm trying to generate some test data out of a production table .... yeah, we can open another thread to discuss if that's a good idea or not.

Anyways, I first used a select with distinct to get all transaction codes currently in the system. Then I get a nice list of all transaction codes. What I want to do is select one, and only one!, row from the prod table that matches to my distinct selection. In other words, simply put, I want to select one example of each type of transaction from the prod table, but in a semi-automated manner. I could just code it manually, but why? The problem is, of course, that all rows are going to be returned when I do something like this:

SELECT B.*,
FROM

(
SELECT distinct trans_type , sub_trans_type
FROM owner.table1
) A
, owner.table1 B
WHERE A.trans_type = B.trans_type
AND A.sub_trans_type = B.sub_trans_type


the output of the select distinct looks like this:

SUB
TRANS TRANS
TYPE TYPE
------ -----
1 A
1 B
1 C
1 D
1 E
1 P
1 W
1 X
3
4
5
7
9
11 S
12 S
13
14
15
16 L
16 N
16 V
17
18 A
18 R
18 S
19
20 D
99
99 K

I mean, the concept is really simple, and I don't wanna have to write some Cobol to solve it....

anyways, thanx in advance!!!! and let's keep those CPU's glowing!

cu l8r, eh?
ralph


edit: PS: the prod table that I want the data from has a PK of several fields (eg. partition, region, account, order #, order originator, control timestamp)...if the key was a single attribute I could have used a group by with min/max for the key....but I don't think that will help me here....

EDIT II:
solved! it's an ugly solution, but it works.....i mashed the primary key together, selected it as a min() and then tore it apart as a join condition to itself.... ..well, see for yourself:

SELECT B.*
FROM

(
SELECT AUFTR_ART , KZ_UNTER_AUFTR_ART
, MIN(
SUBSTR((CHAR(PARTITION)),1,1)
||
SUBSTR((CHAR(KZ_ORTS_KV_EINGEB)),2,3)
||
SUBSTR((CHAR(HPT_KTO_NR_EINGEB)),2,5)
||
SUBSTR((CHAR(AUFTR_NR )),2,7)
||
SUBSTR((CHAR(AUFTR_URSPRUNG )),2,3)
||
SUBSTR((CHAR(ZEITP_KONTROLLE )),2,25)
) AS COMPOUND_KEY
FROM owner.table1
GROUP BY AUFTR_ART , KZ_UNTER_AUFTR_ART
) A
, owner.table1 B
WHERE A.AUFTR_ART = B.AUFTR_ART
AND A.KZ_UNTER_AUFTR_ART = B.KZ_UNTER_AUFTR_ART
AND SUBSTR((CHAR(B.PARTITION)),1,1) = SUBSTR(A.COMPOUND_KEY, 1, 1)
AND SUBSTR((CHAR(KZ_ORTS_KV_EINGEB)),2,3) = SUBSTR(A.COMPOUND_KEY, 2, 3)
AND SUBSTR((CHAR(HPT_KTO_NR_EINGEB)),2,5) = SUBSTR(A.COMPOUND_KEY, 5, 5)
AND SUBSTR((CHAR(AUFTR_NR )),2,7) = SUBSTR(A.COMPOUND_KEY, 10, 7)
AND SUBSTR((CHAR(AUFTR_URSPRUNG )),2,3) = SUBSTR(A.COMPOUND_KEY, 17, 3)
AND SUBSTR((CHAR(ZEITP_KONTROLLE )),2,25) = SUBSTR(A.COMPOUND_KEY, 20, 25)