Home » SQL & PL/SQL » SQL & PL/SQL » Regarding DECODE
Regarding DECODE [message #221256] Mon, 26 February 2007 00:27 Go to next message
orafan2003
Messages: 122
Registered: February 2006
Senior Member
Hi,
I have a SQL query wherein,I am passing employee name as a parameter.
The name of the column is emp_name.
If I enter the name,the result should match for that particular name. Orelse, if I select "All", it should take "%" and return all values. How can I achieve this using decode?
I tried something like this, but it is returning all rows.
decode(emp_name,:name,:name,'%')
Kindly guide me.

Re: Regarding DECODE [message #221266 is a reply to message #221256] Mon, 26 February 2007 01:12 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I do not have Oracle to test this right now but can you check if something like this works for you?

where case when :name = 'All' then '1' else emp_name end = 
case when :name = 'All' then '1' else :name end
/
Re: Regarding DECODE [message #221293 is a reply to message #221256] Mon, 26 February 2007 03:36 Go to previous message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
This ?

SQL> var name varchar2(10)
SQL> exec :name := 'SMITH'

PL/SQL procedure successfully completed.

SQL> select ename from emp where ename like decode(:name,'All','%',:name);

ENAME
----------
SMITH

SQL> exec :name := 'All'

PL/SQL procedure successfully completed.

SQL> select ename from emp where ename like decode(:name,'All','%',:name);

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Rgds.
Previous Topic: user_objects - difference between 2 columns
Next Topic: On Date Change Trigger
Goto Forum:
  


Current Time: Fri Dec 09 15:55:13 CST 2016

Total time taken to generate the page: 0.16166 seconds