Home » SQL & PL/SQL » SQL & PL/SQL » Please help me with sqlplus reporting command (oracle/9i/win xp)
Please help me with sqlplus reporting command [message #359429] Sun, 16 November 2008 01:47 Go to next message
salman4u
Messages: 12
Registered: July 2007
Junior Member
hello,
my requirement is that whenever i fire a query i should get linenumber for each page. The output format is as follows :


Country wise player report
country name :india
1 sachin
2 rahul
3 laxman
Total Players :3

country name:South Africa
1 smith
2 gibbs
Total Players :2

Please tell how do i obtain linenumber on each page starting from 1
Re: Please help me with sqlplus reporting command [message #359433 is a reply to message #359429] Sun, 16 November 2008 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER function.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Please help me with sqlplus reporting command [message #359436 is a reply to message #359429] Sun, 16 November 2008 03:01 Go to previous messageGo to next message
salman4u
Messages: 12
Registered: July 2007
Junior Member
You're the man! Smile

Thanks a lot man. Ok i will post according to the rules next time.
Thanks again Smile
Re: Please help me with sqlplus reporting command [message #359439 is a reply to message #359429] Sun, 16 November 2008 04:01 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you have mentioned "pages" and "totals", perhaps you should also pay attention to the way SQL*Plus creates reports (BREAK and COMPUTE commands might be helpful).

Here's an example based on Scott's EMP schema; perhaps you'll find it useful:
SQL> break on deptno skip page
SQL> compute count of rb on deptno
SQL>
SQL> select deptno,
  2    row_number() over (partition by deptno order by empno) rb,
  3    ename
  4  from emp
  5  order by deptno, empno;
♀    DEPTNO        RB ENAME
---------- ---------- ----------
        10          1 CLARK
                    2 KING
                    3 MILLER
********** ----------
count               3
♀    DEPTNO        RB ENAME
---------- ---------- ----------
        20          1 SMITH
                    2 JONES
                    3 SCOTT
                    4 ADAMS
                    5 FORD
********** ----------
count               5
♀    DEPTNO        RB ENAME
---------- ---------- ----------
        30          1 WARD
********** ----------
count               1
♀    DEPTNO        RB ENAME
---------- ---------- ----------
        40          1 ALLEN
                    2 MARTIN
                    3 BLAKE
                    4 TURNER
                    5 JAMES
********** ----------
count               5

14 rows selected.

SQL>
Re: Please help me with sqlplus reporting command [message #359456 is a reply to message #359429] Sun, 16 November 2008 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way is:
SQL> col deptno noprint
SQL> break on deptno skip 1 on dept
SQL> col dept format a20
SQL> select deptno,
  2         decode(grouping(ename),
  3                1, 'Total dept '||deptno||': '||count(*) over(partition by deptno),
  4                deptno) dept,
  5         decode(grouping(ename),
  6                1, to_number(null),
  7                row_number() over(partition by deptno order by ename)) rn,
  8         ename
  9  from emp
 10  group by rollup(deptno,ename)
 11  having grouping(deptno) = 0
 12  order by deptno, rn
 13  /
DEPT                    RN ENAME
-------------------- ----- ----------
10                       1 CLARK
                         2 KING
                         3 MILLER
Total dept 10: 4

20                       1 ADAMS
                         2 FORD
                         3 JONES
                         4 SCOTT
                         5 SMITH
Total dept 20: 6

30                       1 ALLEN
                         2 BLAKE
                         3 JAMES
                         4 MARTIN
                         5 TURNER
                         6 WARD
Total dept 30: 7


17 rows selected.

If you want a new page each department change "skip 1" to "skip page".

Regards
Michel
Re: Please help me with sqlplus reporting command [message #359474 is a reply to message #359456] Sun, 16 November 2008 14:22 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ummm ... your totals are wrong, Michel.
DEPT                    RN ENAME
-------------------- ----- ----------
10                       1 CLARK     --> one,
                         2 KING      --> two,
                         3 MILLER    --> three, ...
Total dept 10: 4                     --> ... so total should be 3; right?
Re: Please help me with sqlplus reporting command [message #359475 is a reply to message #359474] Sun, 16 November 2008 14:36 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Right! I forgot to remove the rollup row in the count in each group.
So:
SQL> select deptno,
  2         decode(grouping(ename),
  3                1, 'Total dept '||deptno||': '||(count(*) over(partition by deptno)-1),
  4                deptno) dept,
  5         decode(grouping(ename),
  6                1, to_number(null),
  7                row_number() over(partition by deptno order by ename)) rn,
  8         ename
  9  from emp
 10  group by rollup(deptno,ename)
 11  having grouping(deptno) = 0
 12  order by deptno, rn
 13  /
DEPT                    RN ENAME
-------------------- ----- ----------
10                       1 CLARK
                         2 KING
                         3 MILLER
Total dept 10: 3

20                       1 ADAMS
                         2 FORD
                         3 JONES
                         4 SCOTT
                         5 SMITH
Total dept 20: 5

30                       1 ALLEN
                         2 BLAKE
                         3 JAMES
                         4 MARTIN
                         5 TURNER
                         6 WARD
Total dept 30: 6


17 rows selected.

Regards
Michel
Previous Topic: Impact on killing session
Next Topic: strange trigger
Goto Forum:
  


Current Time: Wed Dec 07 04:53:53 CST 2016

Total time taken to generate the page: 0.07834 seconds