Home » SQL & PL/SQL » SQL & PL/SQL » Incremental counter required
Incremental counter required [message #430688] Wed, 11 November 2009 14:19 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I'm working on a sql script that will extract data from an oracle database ver. 10G and load into another system. This will be a comma delimmited file format.

The record specifications require an import id (incremental counter beginning with 1010, numeric field length 4, plus 10 other fields.

Any suggestions on how to create this incremental counter? I'm assuming like a sequence number, but not sure on how this should be created in my script.

Thanks

Anne
Re: Incremental counter required [message #430689 is a reply to message #430688] Wed, 11 November 2009 14:26 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SQL> select rownum+1010 from dual;

ROWNUM+1010
-----------
       1011
Re: Incremental counter required [message #430690 is a reply to message #430688] Wed, 11 November 2009 14:29 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a sequence that starts with 1010 and include it into the SELECT statement. Something like
SQL> create sequence my_seq start with 1010;

Sequence created.

SQL> select my_seq.nextval, d.*
  2  from dept d;

   NEXTVAL     DEPTNO DNAME          LOC
---------- ---------- -------------- ---------------------------
      1010         10 ACCOUNTING     NEW YORK
      1011         20 RESEARCH       DALLAS
      1012         30 SALES          CHICAGO
      1013         40 OPERATIONS     BOSTON

SQL>
Re: Incremental counter required [message #430692 is a reply to message #430690] Wed, 11 November 2009 15:06 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Thank you ...I will try to find out through toad optimizer, which way would be the most effecient, since this sql script will be ran everyday at least 2-3 times a day. Probably creating the actual sequence using .nextval will be better, but I will see.

Thanks again for the prompt response.

Anne
Re: Incremental counter required [message #430694 is a reply to message #430692] Wed, 11 November 2009 15:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the "rownum" one will definetely be the more efficient.

But the deciding factor should probably be:

Do the ID has to start at 1010 at each export? Or does the ID have to start with the next number after the last one of the previous export?
Re: Incremental counter required [message #430695 is a reply to message #430692] Wed, 11 November 2009 15:22 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Rownum incurs "zero" overhead as it's a virtual column and is implicitly there. Each time you run the query it will start from the same number (even if you change the where/order by clause). The sequence number generator will always increment. You'll quickly hit the upper limit of the sequence if you limit max length to 4 (maxvalue 9999). "cycle" allows the seq to wrap around to the minvalue again. Selecting from a seq should add neglegable overhead either (especially if you don't reduce the cache value for the sequence).
Re: Incremental counter required [message #430697 is a reply to message #430694] Wed, 11 November 2009 16:05 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
They do need to be unique, but they do not have to be continuous.

Anne
Re: Incremental counter required [message #430749 is a reply to message #430697] Thu, 12 November 2009 03:37 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thomas asked:
But the deciding factor should probably be:

Do the ID has to start at 1010 at each export? Or does the ID have to start with the next number after the last one of the previous export?


Please answer this

[Updated on: Thu, 12 November 2009 03:39]

Report message to a moderator

Re: Incremental counter required [message #430750 is a reply to message #430697] Thu, 12 November 2009 03:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Anne Simms wrote on Wed, 11 November 2009 23:05
They do need to be unique, but they do not have to be continuous.

Anne

It was answered

[Updated on: Thu, 12 November 2009 03:39]

Report message to a moderator

Re: Incremental counter required [message #430751 is a reply to message #430750] Thu, 12 November 2009 03:40 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
unique at each run time or unique overall(Still not clearly stated)

if unique overall then clearly you cant use the "rownum" feature,left only with sequences option

[Updated on: Thu, 12 November 2009 03:44]

Report message to a moderator

Re: Incremental counter required [message #430848 is a reply to message #430751] Thu, 12 November 2009 09:05 Go to previous message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Good thinking Ayush, they would need to be unique each run. Thanks for all your thoughts and I think we will also need to increase the seq. numbering to at least 6 digits.

Thanks again,

Anne
Previous Topic: problem with multiple "not in" conditions in query
Next Topic: To convert a -ve number to Hexadecimal
Goto Forum:
  


Current Time: Sat Dec 03 01:18:09 CST 2016

Total time taken to generate the page: 0.12995 seconds