Home » SQL & PL/SQL » SQL & PL/SQL » equalent of 'break on' statement of SQL PLUS in SQL
equalent of 'break on' statement of SQL PLUS in SQL [message #11467] Sun, 28 March 2004 22:29 Go to next message
nands
Messages: 1
Registered: March 2004
Junior Member
hi

can anyone help me by telling me the equalent of 'break on' statement of SQL PLUS in SQL.i don't want to use 'rollup' command.
Re: equalent of 'break on' statement of SQL PLUS in SQL [message #11476 is a reply to message #11467] Mon, 29 March 2004 02:35 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please see the following two examples that demonstrate the same results with and without break. I assume that you don't want to use break because you are not excecuting from SQL*Plus. If you are executing in pl/sql prior to version 9i of Oracle you will have to execute it dynamically, because, prior to that, the analytic functions were only available in SQL, not PL/SQL. The same may apply if you are executing from some other calling environment.

scott@ORA92> -- with break:
scott@ORA92> BREAK  ON deptno ON job
scott@ORA92> SELECT deptno, job, ename
  2  FROM   emp
  3  ORDER  BY deptno, job, ename
  4  /

    DEPTNO JOB       ENAME
---------- --------- ----------
        10 CLERK     MILLER
           MANAGER   CLARK
           PRESIDENT KING
        20 ANALYST   FORD
                     SCOTT
           CLERK     ADAMS
                     SMITH
           MANAGER   JONES
        30 CLERK     JAMES
           MANAGER   BLAKE
           SALESMAN  ALLEN
                     MARTIN
                     TURNER
                     WARD

14 rows selected.


scott@ORA92> -- without break:
scott@ORA92> CLEAR  BREAKS
scott@ORA92> SELECT DECODE (deptno_rn, 1, deptno, NULL) AS deptno,
  2  	    DECODE (job_rn   , 1, job	, NULL) AS job,
  3  	    ename
  4  FROM   (SELECT deptno, job, ename,
  5  		    ROW_NUMBER () OVER
  6  		      (PARTITION BY deptno
  7  		       ORDER	 BY job, ename) AS deptno_rn,
  8  		    ROW_NUMBER () OVER
  9  		      (PARTITION BY deptno, job
 10  		       ORDER	 BY ename) AS job_rn
 11  	     FROM   emp
 12  	     ORDER  BY deptno, job, ename)
 13  /

    DEPTNO JOB       ENAME
---------- --------- ----------
        10 CLERK     MILLER
           MANAGER   CLARK
           PRESIDENT KING
        20 ANALYST   FORD
                     SCOTT
           CLERK     ADAMS
                     SMITH
           MANAGER   JONES
        30 CLERK     JAMES
           MANAGER   BLAKE
           SALESMAN  ALLEN
                     MARTIN
                     TURNER
                     WARD

14 rows selected.
Previous Topic: how to find the day of the given date
Next Topic: Need help on gathering Statistics for Partitioned table load
Goto Forum:
  


Current Time: Thu Mar 28 11:32:35 CDT 2024