OraFAQ Forum: SQL & PL/SQL » Break statement in a single SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Break statement in a single SQL

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

Break statement in a single SQL [message #31562]

Tue, 06 July 2004 21:38

Raja Das
Messages: 42
Registered: July 2004

Member

Hi all, Please write a single sql statemet to have the following output. Thanks to all raja4c@sify.com

DEPTNO�� JOB�� EMPNO�� ENAME�� SAL

--------- --------- --------- ---------- ---------�

10��CLERK�� 7934�� MILLER�� 1300

��MANAGER�� 7782�� CLARK��2450

�� PRESIDENT 7839��KING�� 5000

20�� ANALYST�� 7902��FORD��3000

�� 7788�� SCOTT��3000

�� CLERK�� 7369�� SMITH��800

��7876�� ADAMS�� 1100

�� MANAGER� 7566�� JONES�� 2975

30�� CLERK�� 7900�� JAMES�� 950

�� MANAGER� 7698��BLAKE�� 2850

�� SALESMAN 7654��MARTIN��1250

�� 7499�� ALLEN��1600

�� 7521�� WARD��1250

�� 7844�� TURNER��1500

Report message to a moderator

Re: Break statement in a single SQL [message #31565 is a reply to message #31562]

Tue, 06 July 2004 22:04

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Homework?

The select itself is nothing, you are just selecting from emp.

But you'll need an SQL*Plus command here:

SQL> break on deptno on job
SQL> SELECT deptno
  2       , job
  3       , empno
  4       , ename
  5       , sal
  6    FROM emp
  7   ORDER BY deptno, job
  8  /

   DEPTNO JOB           EMPNO ENAME            SAL
--------- --------- --------- ---------- ---------
       10 CLERK          7934 MILLER          1300
          MANAGER        7782 CLARK           2450
          PRESIDENT      7839 KING            5000
       20 ANALYST        7902 FORD            3000
                         7788 SCOTT           3000
          CLERK          7369 SMITH            800
                         7876 ADAMS           1100
          MANAGER        7566 JONES           2975
       30 CLERK          7900 JAMES            950
          MANAGER        7698 BLAKE           2850
          SALESMAN       7499 ALLEN           1600
                         7521 WARD            1250
                         7844 TURNER          1500
                         7654 MARTIN          1250

MHE

Report message to a moderator

Re: Break statement in a single SQL [message #31599 is a reply to message #31565]

Wed, 07 July 2004 18:09

Raja Das
Messages: 42
Registered: July 2004

Member

hi maaher,
yes home work.
but i need only a single sql statement without using sql*plus statement.There is an answer ,as some one has challenged me.so please try to give me a single sql statement.thanks for ur reply.
raja4c@sify.com

Report message to a moderator

Re: Break statement in a single SQL [message #31604 is a reply to message #31599]

Wed, 07 July 2004 23:49

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Sorry, I forgot about analytical functions. You can via this query:

SQL> select decode( lag(deptno) over ( partition by deptno order by  job)
  2               , deptno, to_number(NULL)
  3               , deptno ) dno
  4       , decode( lag(job) over ( partition by deptno, job order by job )
  5               , job, to_char(NULL)
  6               , job ) job
  7       , ename
  8   from emp
  9  order by deptno, job
 10  /

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

14 rows selected.

MHE

Report message to a moderator

Re: Break statement in a single SQL [message #31635 is a reply to message #31604]

Thu, 08 July 2004 22:04

Raja Das
Messages: 42
Registered: July 2004

Member

thanks,
u r really brilliant sir.here i have also got an answer like urs.
1 select decode(rn,1,deptno,null) deptno,
2 decode(rn1,1,job,null) job,
3 empno,
4 ename,
5 sal
6 from
7 (select
8 deptno,
9 job,
10 empno,
11 ename,
12 sal,
13 row_number() over(partition by deptno order by deptno) rn,
14 row_number() over(partition by deptno,job order by deptno,job) rn1
15* from emp);

raja4c@sify.com

Report message to a moderator

Previous Topic:	Regarding certification(oca)
Next Topic:	what is HWM

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 04:51:29 CDT 2024