2 Replies Latest reply: Nov 6, 2010 4:11 AM by Aketi Jyuuzou RSS

    Version Key creation

    591131
      Hi,

      i have a table like

      INSERT INTO my_table(
      SELECT 9591 L_ID, 'GULSU1' NAME, 111 C_ID, to_date('01-NOV-99') VALID_START_DATE FROM DUAL UNION ALL
      SELECT 17107, 'GULSU1', 111, to_date('18-NOV-99') FROM DUAL UNION ALL
      SELECT 164522, 'GULSU1', 111, to_date('21-MAR-00') FROM DUAL UNION ALL
      SELECT 177619, 'GULSU1', 111, to_date('23-APR-00') FROM DUAL UNION ALL
      SELECT 197247, 'GULSU1', 111, to_date('31-MAY-01') FROM DUAL UNION ALL
      SELECT 204492, 'GULSU1', 111, to_date('04-JUN-01') FROM DUAL UNION ALL
      SELECT 211649, 'GULSU1', 111, to_date('12-JUN-01') FROM DUAL UNION ALL
      SELECT 224614, 'GULSU1', 111, to_date('30-APR-03') FROM DUAL UNION ALL
      SELECT 363415, 'GULSU1', 111, to_date('24-SEP-04') FROM DUAL UNION ALL
      SELECT 9592, 'GULSU2', 112, to_date('01-NOV-99') FROM DUAL UNION ALL
      SELECT 17108, 'GULSU2', 112, to_date('18-NOV-99') FROM DUAL UNION ALL
      SELECT 164523, 'GULSU2', 112, to_date('21-MAR-00') FROM DUAL UNION ALL
      SELECT 177620, 'GULSU2', 112, to_date('23-APR-00') FROM DUAL UNION ALL
      SELECT 197248, 'GULSU2', 112, to_date('31-MAY-01') FROM DUAL UNION ALL
      SELECT 204493, 'GULSU2', 112, to_date('04-JUN-01') FROM DUAL UNION ALL
      SELECT 211650, 'GULSU2', 112, to_date('12-JUN-01') FROM DUAL UNION ALL
      SELECT 224615, 'GULSU1', 112, to_date('30-APR-03') FROM DUAL UNION ALL
      SELECT 287725, 'GULSU1', 112, to_date('25-MAR-04') FROM DUAL
      );

      And i select this data with keys like;

      QUERY:

      SELECT
      dense_rank() over (order by C_ID) SK,
      row_number() over (partition by C_ID order by VALID_START_DATE) VK,
      L_ID,
      NAME,
      C_ID,
      VALID_START_DATE
      FROM my_table ORDER BY C_ID,VALID_START_DATE;

      The output of this query is:

      1 | 1 | 9591 | GULSU1 | 111 | 01-NOV-99
      1 | 2 | 17107 | GULSU1 | 111 | 18-NOV-99
      1 | 3 | 164522 | GULSU1 | 111 | 21-MAR-00
      1 | 4 | 177619 | GULSU1 | 111 | 23-APR-00
      1 | 5 | 197247 | GULSU1 | 111 | 31-MAY-01
      1 | 6 | 204492 | GULSU1 | 111 | 04-JUN-01
      1 | 7 | 211649 | GULSU1 | 111 | 12-JUN-01
      1 | 8 | 224614 | GULSU1 | 111 | 30-APR-03
      1 | 9 | 363415 | GULSU1 | 111 | 24-SEP-04
      2 | 1 | 9592 | GULSU2 | 112 | 01-NOV-99
      2 | 2 | 17108 | GULSU2 | 112 | 18-NOV-99
      2 | 3 | 164523 | GULSU2 | 112 | 21-MAR-00
      2 | 4 | 177620 | GULSU2 | 112 | 23-APR-00
      2 | 5 | 197248 | GULSU2 | 112 | 31-MAY-01
      2 | 6 | 204493 | GULSU2 | 112 | 04-JUN-01
      2 | 7 | 211650 | GULSU2 | 112 | 12-JUN-01
      2 | 8 | 224615 | GULSU1 | 112 | 30-APR-03
      2 | 9 | 287725 | GULSU1 | 112 | 25-MAR-04

      I want the result with one more version key that shows the NAME changes. And I want the output like:

      1 | 1 | 1 | 9591 | GULSU1 | 111 | 01-NOV-99
      1 | 2 | 1 | 17107 | GULSU1 | 111 | 18-NOV-99
      1 | 3 | 1 | 164522 | GULSU1 | 111 | 21-MAR-00
      1 | 4 | 1 | 177619 | GULSU1 | 111 | 23-APR-00
      1 | 5 | 1 | 197247 | GULSU1 | 111 | 31-MAY-01
      1 | 6 | 1 | 204492 | GULSU1 | 111 | 04-JUN-01
      1 | 7 | 1 | 211649 | GULSU1 | 111 | 12-JUN-01
      1 | 8 | 1 | 224614 | GULSU1 | 111 | 30-APR-03
      1 | 9 | 1 | 363415 | GULSU1 | 111 | 24-SEP-04
      2 | 1 | 1 | 9592 | GULSU2 | 112 | 01-NOV-99
      2 | 2 | 1 | 17108 | GULSU2 | 112 | 18-NOV-99
      2 | 3 | 1 | 164523 | GULSU2 | 112 | 21-MAR-00
      2 | 4 | 1 | 177620 | GULSU2 | 112 | 23-APR-00
      2 | 5 | 1 | 197248 | GULSU2 | 112 | 31-MAY-01
      2 | 6 | 1 | 204493 | GULSU2 | 112 | 04-JUN-01
      2 | 7 | 1 | 211650 | GULSU2 | 112 | 12-JUN-01
      2 | 8 | 2 | 224615 | GULSU1 | 112 | 30-APR-03
      2 | 9 | 2 | 287725 | GULSU1 | 112 | 25-MAR-04


      How may i do this with a single select statement???

      Thanks, BR...
        • 1. Re: Version Key creation
          450441
          Isn't DENSE_RANK() OVER (partition by c_id, name ... ) what you want?
          • 2. Re: Version Key creation
            Aketi Jyuuzou
            with WorkView as (
            SELECT 'GULSU1' NAME, 111 C_ID,10 as SortKey FROM DUAL UNION
            SELECT 'GULSU1', 111,20 FROM DUAL UNION
            SELECT 'GULSU1', 111,30 FROM DUAL UNION
            SELECT 'GULSU1', 111,40 FROM DUAL UNION
            SELECT 'GULSU1', 111,50 FROM DUAL UNION
            SELECT 'GULSU1', 111,60 FROM DUAL UNION
            SELECT 'GULSU1', 111,70 FROM DUAL UNION
            SELECT 'GULSU1', 111,80 FROM DUAL UNION
            SELECT 'GULSU1', 111,90 FROM DUAL UNION
            SELECT 'GULSU2', 112,100 FROM DUAL UNION
            SELECT 'GULSU2', 112,110 FROM DUAL UNION
            SELECT 'GULSU2', 112,120 FROM DUAL UNION
            SELECT 'GULSU2', 112,130 FROM DUAL UNION
            SELECT 'GULSU2', 112,140 FROM DUAL UNION
            SELECT 'GULSU2', 112,150 FROM DUAL UNION
            SELECT 'GULSU2', 112,160 FROM DUAL UNION
            SELECT 'GULSU1', 112,170 FROM DUAL UNION
            SELECT 'GULSU1', 112,180 FROM DUAL)
            select NAME,C_ID,SortKey,
            sum(willSum) over(partition by NAME order by SortKey) as version
            from (select NAME,C_ID,SortKey,
                   case when lnnvl(NAME = Lag(NAME) over(order by SortKey))
                        then 1 else 0 end as willSum
                   from WorkView)
            order by SortKey;
            
            NAME    C_ID  SortKey  version
            ------  ----  -------  -------
            GULSU1   111       10        1
            GULSU1   111       20        1
            GULSU1   111       30        1
            GULSU1   111       40        1
            GULSU1   111       50        1
            GULSU1   111       60        1
            GULSU1   111       70        1
            GULSU1   111       80        1
            GULSU1   111       90        1
            GULSU2   112      100        1
            GULSU2   112      110        1
            GULSU2   112      120        1
            GULSU2   112      130        1
            GULSU2   112      140        1
            GULSU2   112      150        1
            GULSU2   112      160        1
            GULSU1   112      170        2
            GULSU1   112      180        2
            On Oracke9i,
            we must emulate lnnvl.

            similar thread
            Group by preserving the order

            and my site :-)
            http://www.geocities.jp/oraclesqlpuzzle/10-130.html