Home » SQL & PL/SQL » SQL & PL/SQL » Replace (Oracle, 10g, Windows Server 2003)
Replace [message #442941] Thu, 11 February 2010 09:13 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: NVL date return value
Next Topic: Limit number of rows (not what you think)
Goto Forum:
  


Current Time: Fri Feb 14 08:55:13 CST 2025