Replace [message #442941] |
Thu, 11 February 2010 09:13  |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |

|
|
Hi,
Following is Create table sql statement:
SQL> Create Table Emp_Demo(Deptno Number,Dep Varchar2(5));
Table Created.
Following are insert table scripts:
SQL> insert into emp_demo values(10,'EDU');
1 row inserted.
SQL> insert into emp_demo values(20,'EDU');
1 row inserted.
SQL> insert into emp_demo values(30,'EDU');
1 row inserted.
SQL> select * from emp_demo;
[b]DEPTNO DEP[/b]
10 EDU
20 EDU
30 EDU
3 rows selected
I executed following statement
SQL> SELECT DISTINCT DEP, CASE WHEN DEPTNO =10 THEN 'EDUCATION'
END CHK FROM EMP_DEMO;
[b]DEP CHK[/b]
EDU
EDU EDUCATION
Is it possible that EDUCATION in the second row also comes in
first row, so that we get only one row as output because
of DISTINCT keyword used in the query, such as
[b]DEP CHK[/b]
EDU EDUCATION
Regards,
Ritesh
|
|
|
Re: Replace [message #442942 is a reply to message #442941] |
Thu, 11 February 2010 09:21   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
SQL> ed
Wrote file afiedt.buf
1 select dep,max(chk)
2 from(SELECT DISTINCT DEP, CASE WHEN DEPTNO =10 THEN 'EDUCATION'
3 END CHK FROM EMP_DEMO)
4* group by dep
SQL> /
DEP MAX(CHK)
----- ---------
EDU EDUCATION
SQL> select dep,max(CASE WHEN DEPTNO =10 THEN 'EDUCATION' END )
2 FROM EMP_DEMO
3 group by dep
4 /
DEP MAX(CASEW
----- ---------
EDU EDUCATION
[Updated on: Thu, 11 February 2010 09:22] Report message to a moderator
|
|
|
Re: Replace [message #442943 is a reply to message #442942] |
Thu, 11 February 2010 09:25   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
SQL> Create Table Emp_Demo(Deptno Number,Dep Varchar2(5));
Table created.
SQL> insert into emp_demo values(10,'EDU');
1 row created.
SQL> insert into emp_demo values(20,'EDU');
1 row created.
SQL> SQL> insert into emp_demo values(30,'EDU');
1 row created.
SQL> commit;
Commit complete.
SQL> select deptno, decode(dep,'EDU','EDUCATION') from emp_demo where deptno = 10;
DEPTNO DECODE(DE
---------- ---------
10 EDUCATION
SQL>
[Updated on: Thu, 11 February 2010 09:26] Report message to a moderator
|
|
|
Re: Replace [message #442946 is a reply to message #442942] |
Thu, 11 February 2010 09:38   |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |

|
|
Hi,
This is query on same table.See the following query and output
SELECT DISTINCT DEP,
CASE WHEN DEPTNO =10 THEN 'EDUCATION'END First,
CASE WHEN DEPTNO =20 THEN 'ACCOUNTS' END Second,
CASE WHEN DEPTNO =30 THEN 'RESEARCH' END Third
FROM EMP_DEMO
ORDER BY DEP;
[b]DEP FIRST SECOND THIRD[/b]
EDU EDUCATION
EDU ACCOUNTS
EDU RESEARCH
Is it possible to show only one record in this case
Regards,
Ritesh
[Updated on: Thu, 11 February 2010 09:49] by Moderator Report message to a moderator
|
|
|
Re: Replace [message #442948 is a reply to message #442946] |
Thu, 11 February 2010 09:49  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> select dep,
2 max(CASE WHEN DEPTNO =10 THEN 'EDUCATION'END) first,
3 max(CASE WHEN DEPTNO =20 THEN 'ACCOUNTS' END) second,
4 max(CASE WHEN DEPTNO =30 THEN 'RESEARCH' END) third
5 FROM EMP_DEMO
6 group by dep
7 /
DEP FIRST SECOND THIRD
----- --------- -------- --------
EDU EDUCATION ACCOUNTS RESEARCH
1 row selected.
Regards
Michel
|
|
|