OraFAQ Forum: SQL & PL/SQL » dumping a table in two halves

Home » SQL & PL/SQL » SQL & PL/SQL » dumping a table in two halves

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

dumping a table in two halves [message #17991]

Mon, 07 February 2005 09:48

Nick
Messages: 64
Registered: February 2000

Member

I need to dump the contents of a table into an excel spreadsheet but the table has more rows thant the spreadsheet can handle.
Easy, I thought, take the first half of the table (by rownum) - done.

But how do I dump the second half of the table? and be sure that the two halves are unique?

Thanks

Nick.

Report message to a moderator

Re: dumping a table in two halves [message #17993 is a reply to message #17991]

Mon, 07 February 2005 10:47

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

What on earth is a user going to do with over 65,000 rows of raw data?

Report message to a moderator

Re: dumping a table in two halves [message #18021 is a reply to message #17993]

Tue, 08 February 2005 00:21

Nick
Messages: 64
Registered: February 2000

Member

It's a work table (not accessible via any application) used to drive another process. Supplying it to the user is just a courtesy as it provides raw information on data destinations.

Report message to a moderator

Re: dumping a table in two halves [message #18077 is a reply to message #18021]

Wed, 09 February 2005 00:11

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Here's one way. Each part of the union gives you a half of the emp table:

SQL> SELECT 'part 1' part
  2       , v.empno
  3       , v.ename
  4       , v.rn1
  5       , v.rn2
  6    FROM (  select empno
  7                 , ename
  8                 , ROW_NUMBER() OVER ( ORDER BY ROWID ASC ) rn1
  9                 , ROW_NUMBER() OVER ( ORDER BY ROWID DESC ) rn2
 10              FROM emp
 11         ) v
 12   WHERE rn1 > rn2
 13  UNION
 14  SELECT 'part 2' part
 15       , v.empno
 16       , v.ename
 17       , v.rn1
 18       , v.rn2
 19    FROM (  select empno
 20                 , ename
 21                 , ROW_NUMBER() OVER ( ORDER BY ROWID ASC ) rn1
 22                 , ROW_NUMBER() OVER ( ORDER BY ROWID DESC ) rn2
 23              FROM emp
 24         ) v
 25   WHERE rn1 <= rn2
 26  /

PART        EMPNO ENAME             RN1        RN2
------ ---------- ---------- ---------- ----------
part 1       7788 SCOTT               8          7
part 1       7839 KING                9          6
part 1       7844 TURNER             10          5
part 1       7876 ADAMS              11          4
part 1       7900 JAMES              12          3
part 1       7902 FORD               13          2
part 1       7934 MILLER             14          1
part 2       7369 SMITH               1         14
part 2       7499 ALLEN               2         13
part 2       7521 WARD                3         12
part 2       7566 JONES               4         11

PART        EMPNO ENAME             RN1        RN2
------ ---------- ---------- ---------- ----------
part 2       7654 MARTIN              5         10
part 2       7698 BLAKE               6          9
part 2       7782 CLARK               7          8

14 rows selected.

SQL>

MHE

Report message to a moderator

Previous Topic:	Deleting multiple rows from table.
Next Topic:	SQL Looping question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 19 16:23:40 CDT 2026