Hi All,
Here is the sample current code.
Actually we were using ROWNUM seq, Ceil (ROWNUM/2900) * 10 batch
for every 2900 sequence of rows populating with 10 ,20 etc..
SELECT 1 ENTRY no,
12 cr,
NULL dr,
11223 header id,
1 line no,
1 seq,
10 batch
FROM dual
UNION ALL
SELECT 1 ENTRY no,
NULL cr,
10 dr,
11223 header id,
2 line no,
2 seq,
10 batch
FROM dual
UNION ALL
SELECT 1 ENTRY no,
NULL cr,
2 dr,
11223 header id,
2 line no,
3 seq,
10 batch
FROM dual
UNION ALL
SELECT 1 ENTRY no,
10 cr,
NULL dr,
3333 header id,
1 line no,
4 seq,
10 batch
FROM dual
UNION ALL
SELECT 1 ENTRY no,
20 cr,
NULL dr,
3333 header id,
2 line no,
5 seq,
10 batch
FROM dual
UNION ALL
SELECT 1 ENTRY no,
NULL cr,
10 dr,
3333 header id,
3 line no,
6 seq,
10 batch
FROM dual
UNION ALL
SELECT 2 ENTRY no,
NULL cr,
10 dr,
3333 header id,
4 line no,
7 seq,
10 batch
FROM dual
UNION ALL
SELECT 2 ENTRY no,
NULL cr,
5 dr,
3333 header id,
5 line no,
8 seq,
20 batch
FROM dual
UNION ALL
SELECT 2 ENTRY no,
NULL cr,
5 dr,
3333 header id,
6 line no,
9 seq,
20 batch
FROM dual
UNION ALL
SELECT 2 ENTRY no,
10 cr,
NULL dr,
44444 header id,
1 line no,
10 seq,
20 batch
FROM dual
UNION ALL
SELECT 2 ENTRY no,
NULL cr,
10 dr,
44444 header id,
2 line no,
11 seq,
20 batch
FROM dual
here is the current sample output.
My header_id 3333(line 9,10 rows are coming in 20 batch , but i need to populate batch as 10 since header id value is same.
Note: populate same batch if header id is same.
Oracle : 11.2
thanks