Home » SQL & PL/SQL » SQL & PL/SQL » how to generate one id for 'x' number of records in a single sql query (Orace 10.2.0.3, Windows)
how to generate one id for 'x' number of records in a single sql query [message #395263] Tue, 31 March 2009 23:53 Go to next message
saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad
Junior Member
Dear All,

I have a requirement like below.

Item table is having below records

Item   Loc   Qty
100    HYD    90
200    HYD    80
789    MUM    87
876    BLR    76
453    JK     67
110    CHE    89
100    HYD    90
200    HYD    80
789    MUM    87
876    BLR    76

Now i would like to generate data like below.

Jobid       Item   Loc   Qty
00001       100    HYD    90
00001       200    HYD    80
00001       789    MUM    87
00001       876    BLR    76
00001       453    JK     67
00002       110    CHE    89
00002       100    HYD    90
00002       200    HYD    80
00002       789    MUM    87
00002       876    BLR    76

in the above sample date we have total 10 records, i need to generate unique id for each 5 years like above (in this case we have 10, it could be 100 as well).

Please help me out with your valid suggestions. I need this in a SQL only.
Thanks

[Updated on: Wed, 01 April 2009 01:45] by Moderator

Report message to a moderator

Re: how to generate one id for 'x' number of records in a single sql query [message #395267 is a reply to message #395263] Wed, 01 April 2009 00:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> i need to generate unique id for each 5 years like above
OK, I give up.
What is "unique id" in what you posted?
I see nothing labeled as such.

From where did/does "Jobid" originate?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: how to generate one id for 'x' number of records in a single sql query [message #395278 is a reply to message #395267] Wed, 01 April 2009 00:42 Go to previous messageGo to next message
saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad
Junior Member
Sorry for the word "unique id", i am looking for a id for 'x' records which is jobid.


The Jobid is a generated key in sql itself, converting date into Jobid field using to_char(sysdate,'mmddyymmss'.

the data in my item table is like this

Item Loc Qty
100 HYD 90
200 HYD 80
789 MUM 87
876 BLR 76
453 JK 67
110 CHE 89
100 HYD 90
200 HYD 80
789 MUM 87
876 BLR 76

my expected resutls are

Jobid Item Loc Qty
00001 100 HYD 90
00001 200 HYD 80
00001 789 MUM 87
00001 876 BLR 76
00001 453 JK 67
00002 110 CHE 89
00002 100 HYD 90
00002 200 HYD 80
00002 789 MUM 87
00002 876 BLR 76




Thanks

[Updated on: Wed, 01 April 2009 00:43]

Report message to a moderator

Re: how to generate one id for 'x' number of records in a single sql query [message #395281 is a reply to message #395278] Wed, 01 April 2009 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Based on what does your "110 CHE 89" record get a jobid of 00002 ?
Remember that records in tables are not sorted. This means that the phrase "the first n records" only makes sense if you also say what to order by.
Re: how to generate one id for 'x' number of records in a single sql query [message #395284 is a reply to message #395281] Wed, 01 April 2009 01:07 Go to previous messageGo to next message
saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad
Junior Member
Hi Frank,

This exercies is about to create a spool file and send it to another server. And the query will have
"order by item"


Thanks
Re: how to generate one id for 'x' number of records in a single sql query [message #395298 is a reply to message #395284] Wed, 01 April 2009 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue:
SQL> with data as (select trunc(dbms_random.value(100,1000)) item from dual connect by level <= 10)
  2  select item, trunc((row_number() over(order by item)-1)/5)+1 seq
  3  from data
  4  order by 1
  5  /
      ITEM        SEQ
---------- ----------
       113          1
       200          1
       224          1
       247          1
       249          1
       374          2
       420          2
       624          2
       661          2
       695          2

10 rows selected.

Regards
Michel

[Updated on: Wed, 01 April 2009 01:55]

Report message to a moderator

Re: how to generate one id for 'x' number of records in a single sql query [message #395303 is a reply to message #395298] Wed, 01 April 2009 02:01 Go to previous message
saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad
Junior Member
thanks Michel, it will surely help me.
Previous Topic: Size of a clob [Split message]
Next Topic: external tables
Goto Forum:
  


Current Time: Sun Dec 04 06:18:11 CST 2016

Total time taken to generate the page: 0.07248 seconds