Hello frnds,
I've 2 sequences s11, s12. and 3 tables test , test_1 and test_2.
I need to create a table the second column should be from test_1 and 3rd column should be populated from test_2 table.
If the corresponding values are not found then the value should be populated from
the corresponding sequence next value ( s11 seq for col 2 )
and ( s12 seq for col 2 )
I've tried the following methods but the sequence number was jumping to the next number when the cosrresponding value is fetched.
###############
SQL> select c1 , nvl(c12,s11.nextval) , nvl(c22,s12.nextval)
from test a , test_1 b ,test_2 c
where a.c1 = b.c11(+)
and a.c1 = c.c21(+)
--order by c1
;
2 3 4 5 6
C1 NVL(C12,S11.NEXTVAL) NVL(C22,S12.NEXTVAL)
---------- -------------------- --------------------
7 19 8
18 20 57
1 2 58
Note
-------
last number for the sequence was 20 , next time it should start with 21 , instead it is starting with 22, missing the value 21.
I'd be glad if there is any method of solving the same.
SQL> /
C1 NVL(C12,S11.NEXTVAL) NVL(C22,S12.NEXTVAL)
---------- -------------------- --------------------
7 22 8
18 23 60
1 2 61
#########
SQL> select c1 , decode(c12,NULL,s11.nextval,c12) , decode(c22,NULL,s12.nextval,c22)
from test a , test_1 b ,test_2 c
where a.c1 = b.c11(+)
and a.c1 = c.c21(+)
--order by c1
;
2 3 4 5 6
C1 DECODE(C12,NULL,S11.NEXTVAL,C12) DECODE(C22,NULL,S12.NEXTVAL,C22)
---------- -------------------------------- --------------------------------
7 25 8
18 26 63
1 2 64
SQL> /
C1 DECODE(C12,NULL,S11.NEXTVAL,C12) DECODE(C22,NULL,S12.NEXTVAL,C22)
---------- -------------------------------- --------------------------------
7 28 8
18 29 66
1 2 67