Results 1 to 1 of 1

Thread: join question

  1. #1
    Join Date
    Nov 2005
    Posts
    15

    Lightbulb Unanswered: join question RESOLVED!

    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)
    Last edited by nuck; 11-15-05 at 10:00. Reason: prob solved!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •