Home » SQL & PL/SQL » SQL & PL/SQL » SEQ NR Generation based on Code (Oracle 10g)
SEQ NR Generation based on Code [message #438977] Wed, 13 January 2010 06:03 Go to next message
pstanand
Messages: 98
Registered: February 2005
Location: Chennai,India
Member
Hi,
I have a requirement. I need to read from a file and insert a table. The table has following columns.
ST_CD  NUMBER
GRP_NR NUMBER
SEQ_NR NUMBER

The file contain different ST_CD. But no GRP_NR and SEQ_NR. But in the table I need to insert in the following manner. Suppose the ST_CD 15 has more than 30 counts then SEQ_NR will have 1 to 30 and after reaching SEQ_NR=30 the GRP_NR becomes 2 and SEQ_NR starts from 1. I'm unable to form this logic while inserting. Could any body help me in this.
ST_CD	GRP_NR	SEQ_NR
15	1	1
15	1	2
15	1	3
63	1	1
63	1	2
63	1	3
877	1	1
877	1	2

Thanks in advance.

Regards
pstanand.

[EDITED by LF: applied [pre] tags]

[Updated on: Wed, 13 January 2010 06:43] by Moderator

Report message to a moderator

Re: SEQ NR Generation based on Code [message #438979 is a reply to message #438977] Wed, 13 January 2010 06:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd do this in 2 passes:

1) Load the ST_CD values.
2) Update the table and use the analytic function ROW_NUMBER. Set the SEQ_NR to mod(ROW_NUMBER-1,30)+1, and set GRP_NO to FLOOR(ROW_NUMBER/30)

Re: SEQ NR Generation based on Code [message #438984 is a reply to message #438977] Wed, 13 January 2010 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with lines as (select level line from dual connect by level <= 50)
  2  select line,
  3         trunc(row_number() over (order by line)/30)+1 grp,
  4         mod(row_number() over (order by line)-1,30)+1 seq
  5  from lines
  6  /
      LINE        GRP        SEQ
---------- ---------- ----------
         1          1          1
         2          1          2
         3          1          3
         4          1          4
         5          1          5
         6          1          6
         7          1          7
         8          1          8
         9          1          9
        10          1         10
        11          1         11
        12          1         12
        13          1         13
        14          1         14
        15          1         15
        16          1         16
        17          1         17
        18          1         18
        19          1         19
        20          1         20
        21          1         21
        22          1         22
        23          1         23
        24          1         24
        25          1         25
        26          1         26
        27          1         27
        28          1         28
        29          1         29
        30          2         30
        31          2          1
        32          2          2
        33          2          3
        34          2          4
        35          2          5
        36          2          6
        37          2          7
        38          2          8
        39          2          9
        40          2         10
        41          2         11
        42          2         12
        43          2         13
        44          2         14
        45          2         15
        46          2         16
        47          2         17
        48          2         18
        49          2         19
        50          2         20

50 rows selected.

Regards
Michel
Re: SEQ NR Generation based on Code [message #438990 is a reply to message #438984] Wed, 13 January 2010 07:50 Go to previous message
pstanand
Messages: 98
Registered: February 2005
Location: Chennai,India
Member
Thanks JRowbottom. It works fine for me.
Previous Topic: how to read the next record
Next Topic: create a autorunning trigger..(merged)
Goto Forum:
  


Current Time: Sun Sep 25 06:07:32 CDT 2016

Total time taken to generate the page: 0.05125 seconds