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 distinct trans_type , sub_trans_type
, 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:
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?
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....
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 AUFTR_ART , KZ_UNTER_AUFTR_ART
) AS COMPOUND_KEY
GROUP BY AUFTR_ART , KZ_UNTER_AUFTR_ART
, 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)
Last edited by nuck; 11-15-05 at 09:00.
Reason: prob solved!