Home » SQL & PL/SQL » SQL & PL/SQL » Row Generator And Serial Number (10g)
Row Generator And Serial Number [message #432116] Sat, 21 November 2009 05:50 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

I have query which return data as


1 1 250
2 1 150

i need query which should return 250 rows for 1 and 150 rows for 2 with serial number and serial should start from 1 for each.

Regards
Asif.
Re: Row Generator And Serial Number [message #432119 is a reply to message #432116] Sat, 21 November 2009 06:45 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.orafaq.com/forum/t/95011/102589/

sriram Smile
Re: Row Generator And Serial Number [message #432142 is a reply to message #432116] Sat, 21 November 2009 23:49 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear
My query return 2 rows, i have tried "GROUP BY CUBE" but rows are generated for 1st record. Not for second record. I want to generate 250 rows for 1st record 1 and 150 for 2nd record in single query , also want to start serial number from 1 for both.

Regards.
Asif.
Re: Row Generator And Serial Number [message #432143 is a reply to message #432142] Sun, 22 November 2009 00:10 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>My query return 2 rows
Too bad for all concerned.
Your query fails, but we can not offer suggestions because we don't know what went wrong.

Re: Row Generator And Serial Number [message #432144 is a reply to message #432142] Sun, 22 November 2009 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's easy just fix your query in the folloxing way and it will work.

Regards
Michel
Re: Row Generator And Serial Number [message #432201 is a reply to message #432116] Sun, 22 November 2009 23:46 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle
I think i am unable to put my question. Sir my query return following two record.

1 1 250
2 1 150

i have generate to generate 250 rows for record 1 and 150 rows for record 2 in one query.

Best Regards.
Asif.
Re: Row Generator And Serial Number [message #432202 is a reply to message #432201] Sun, 22 November 2009 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We understood your requirements.
What we don't understand is your query.

Regards
Michel
Re: Row Generator And Serial Number [message #432206 is a reply to message #432116] Mon, 23 November 2009 00:13 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

DEAR MICHLE UNCLE.
I am unable to understand your answer.
Quote:
It's easy just fix your query in the folloxing way and it will work.


please explain.

Regards.
Asif.
Re: Row Generator And Serial Number [message #432207 is a reply to message #432201] Mon, 23 November 2009 00:20 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> create table test
  2  (
  3  a number,
  4  b number,
  5  c number
  6  );

Table created.

SQL> insert into test values(1,1,250);

1 row created.

SQL> insert into test values(2,1,150);

1 row created.

SQL> commit;

Commit complete.

SELECT   a, b, c, rn
    FROM TEST,
         (SELECT     ROWNUM rn
                FROM DUAL
          CONNECT BY LEVEL <= 500)
   WHERE c >= rn
ORDER BY 1, 2, 3, 4
Re: Row Generator And Serial Number [message #432210 is a reply to message #432207] Mon, 23 November 2009 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once more DO NOT POST solution, post hint/clue and let OP posts what he find.

Regards
Michel
Re: Row Generator And Serial Number [message #432212 is a reply to message #432210] Mon, 23 November 2009 00:53 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
sorry
Re: Row Generator And Serial Number [message #432213 is a reply to message #432116] Mon, 23 November 2009 00:53 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Great Dear
But i am unable to under
Quote:
WHERE c >= rn


i am thinking that it should be
WHERE c <= rn


please explain why

Quote:
WHERE c >= rn


Thank you very much & Best Regards
Asif.
Re: Row Generator And Serial Number [message #432214 is a reply to message #432213] Mon, 23 November 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please once again post what you tried and explain what does "But i am unable to under" means.

Regards
Michel
Re: Row Generator And Serial Number [message #432217 is a reply to message #432213] Mon, 23 November 2009 01:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
WHERE c >= rn


means select rownum lesser than c i.e. if c is 150 it will select 1 to 150

Quote:
WHERE c <= rn


means select rownum greater than c i.e. if c is 150 it will select 150 to maximum number of rows generated by your row generator
Re: Row Generator And Serial Number [message #432529 is a reply to message #432116] Tue, 24 November 2009 16:53 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
mamalik

The answer to your questions is 1) use of row generator, and 2) use of rownum. I believe you will find what you need if you take the time to read the link that was given to you. It has good material on row generators for oracle, and additional links for additional reading.

Read the link, then get back to us with the solution you figured out from the link. Yes, once you read the link you will figure out an answer.

Good luck, Kevin
Previous Topic: I want to Convert rows to columns (merged 3)
Next Topic: Error Executing Package/Procedure
Goto Forum:
  


Current Time: Thu Sep 29 00:33:21 CDT 2016

Total time taken to generate the page: 0.09409 seconds