OraFAQ Forum: SQL & PL/SQL » How to get rows N times in a result set?

Home » SQL & PL/SQL » SQL & PL/SQL » How to get rows N times in a result set?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get rows N times in a result set? [message #446567]

Tue, 09 March 2010 06:15

terber
Messages: 1
Registered: March 2010

Junior Member

How to get rows N times in a result set?

For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once.

I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.

But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".

How can I achieve my goal?

[Updated on: Tue, 09 March 2010 07:40] by Moderator

Report message to a moderator

Re: How to get rows N times in a result set? [message #446571 is a reply to message #446567]

Tue, 09 March 2010 06:21

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

One record:

SQL> select * from dept where deptno = 40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Many records:

SQL> select *
  2  from (select * from dept where deptno = 40)
  3  connect by level <= 10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON

10 rows selected.

SQL>

Report message to a moderator

Re: How to get rows N times in a result set? [message #446578 is a reply to message #446571]

Tue, 09 March 2010 06:35

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Take care this only works with a result set with one row.

Better use:

SQL> select ename, deptno from emp where deptno = 10;
ENAME          DEPTNO
---------- ----------
CLARK              10
KING               10
MILLER             10

3 rows selected.

SQL> with lines as (select level line from dual connect by level <= 5)
  2  select ename, deptno
  3  from emp, lines
  4  where deptno = 10
  5  /
ENAME          DEPTNO
---------- ----------
CLARK              10
KING               10
MILLER             10
CLARK              10
KING               10
MILLER             10
CLARK              10
KING               10
MILLER             10
CLARK              10
KING               10
MILLER             10
CLARK              10
KING               10
MILLER             10

15 rows selected.

Regards
Michel

[Updated on: Tue, 09 March 2010 06:35]

Report message to a moderator

Previous Topic:	Preventing duplicate column value using Trigger
Next Topic:	DBMS_JOB

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 23 14:01:09 CDT 2024