SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Version Key creation

591131Sep 27 2007 — edited Nov 6 2010

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...

450441

Isn't DENSE_RANK() OVER (partition by c_id, name ... ) what you want?

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
450745

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

1 - 2

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 25 2007

Added on Sep 27 2007

2 comments

3,482 views

SQL & PL/SQL

Version Key creation

Comments

Post Details