Home » SQL & PL/SQL » SQL & PL/SQL » start stop rowcount in query (oracle 9g, windows)
start stop rowcount in query [message #428683] Thu, 29 October 2009 08:16 Go to next message
gtriant
Messages: 42
Registered: September 2006
Member
Hello.

I need to produce a table that in the first column there will be a counter. However this counter will be stopped for a row of dashes and then continued. The rows of the tables before and after the dashes come from different queries using union.

Example:

# Name Surname
1 Jack Black
2 Greg Green
- - -
3 Nick Brown
4 Jake White
- - -
5 Mary Lou
etc.

The above can continue....
So the question is how can i stop and start the counting.
IT MUST BE DONE BY A SINGLE QUERY (not a script!).
I cannot do any addition or alteration to the database (no function, no new sequence etc)!!!

What i have done so far is sth like this:
select to_char(rownum), name, surname from table1 where ...
union all
select '-', '-', '-' from dual
union all
select to_char(rownum + 
(select count(*) from table1 where ...)
), name, surname from table2 where ...


The above works, but i cant keep adding the record counts of all the above unions forever!!!!
Anything better?????
Re: start stop rowcount in query [message #428690 is a reply to message #428683] Thu, 29 October 2009 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually the above isn't guaranteed to work. Oracle could reorder the data set and there is no order by clause you could use to stop it.

This is a formatting problem - pure SQL isn't really designed to deal with formatting problems - that's for the client program that displays the data to sort out.
What program are you using to display the data?
Re: start stop rowcount in query [message #428695 is a reply to message #428683] Thu, 29 October 2009 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select rownum, name, surname
from ( select 1 tabnb, name, surname from tablea where ... 
       union all
       select 1.5, '-', '-', '-' from dual
       union all
       select 2, name, surname from tableb where ... 
       order by 1 )
/

Regards
Michel

[Updated on: Thu, 29 October 2009 08:51]

Report message to a moderator

Re: start stop rowcount in query [message #428792 is a reply to message #428683] Fri, 30 October 2009 01:57 Go to previous messageGo to next message
gtriant
Messages: 42
Registered: September 2006
Member
Thank you for your time Michel.
However your query doesn't solve my problem!
You still count the row with the dashes...

I suppose there is nothing left... right?
Re: start stop rowcount in query [message #428798 is a reply to message #428792] Fri, 30 October 2009 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It was just a first and quick draft. It is easy to fix it in what you want for instance (next time post a test case):
SQL> select decode(ename,
  2                '-',to_number(null),
  3                sum(decode(ename,'-',0,1))
  4                  over(order by ord rows between unbounded preceding and current row)
  5               ) rn, 
  6         ename
  7  from ( select 1 ord, ename from emp where rownum<=3
  8         union all
  9         select 1.5, '-' from dual
 10         union all 
 11         select 2, ename from emp where rownum<=5
 12       )
 13  order by ord
 14  /
        RN ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
           -
         4 SMITH
         5 ALLEN
         6 WARD
         7 JONES
         8 MARTIN

Regards
Michel

[Updated on: Fri, 30 October 2009 02:17]

Report message to a moderator

Re: start stop rowcount in query [message #428803 is a reply to message #428798] Fri, 30 October 2009 02:33 Go to previous message
gtriant
Messages: 42
Registered: September 2006
Member
...if you call that easy !!!!!!!!!!

Thanks again
Previous Topic: Update using sample(1)
Next Topic: MERGE statment in oracle pl/sql
Goto Forum:
  


Current Time: Thu Dec 05 20:00:17 CST 2024