SQL & PL/SQL

Hello Gurus,

I'm loading data from staging table to target and need to generate sequence while loading target. Using below tables.


Table 1 SRC (staging table) Table 2 : TGT ( target table)

ID name Sal Seq ID name Sal
-- ---- --- --- -- ---- ---
10 ‘abc’ 200 1 10 ‘abc’ 200
20 ‘sdf’ 300
30 'ghr' 400
40 'ghk' 500

My query is :

select t.ID,t.name,t.sal,decode(t.seq,0,SEQUENCE.nextval,1,NULL) as seq
from
(select src.ID ,src.name ,src.sal ,decode(tgt.seq,NULL,0,1) seq
from SRC
left outer join TGT
on src.id = tgt.id ) t

1st RUN :

ID name Sal seq
--- ---- ---- ----
10 ‘abc’ 200 NULL
20 ‘sdf’ 300 1
30 'ghr' 400 2
40 'ghk' 500 *3*
2nd RUN :

ID name Sal seq
--- --- --- ---
10 ‘abc’ 200 NULL
20 ‘sdf’ 300 *5*
30 'ghr' 400 6
40 'ghk' 500 7


Every time i execute the same query there is gap in sequence ( after 3 , 4 is missing. ).
Please let me know what im missing here.
or how to avoid the gap in sequence.

Using Oracle 10g Release 10.2.0.1.0

Thanks !