Oracle Query Conditional in clause

Question

0.00/5 (No votes)

See more:

i create a function in db that return me employee name ,

SQL

function getData(pname varchar2 default null) return varchar2 is
presults varchar2(1000);
begin
 select listagg(emp_name,',') within group (order by null) into presults
 from employee
 where (case when pname is null then 1 else emp_name end ) in
        (case when pname is null then 1 else (select emp_name from employee where emp_name like '%'||pname||'%') end );
 return presults;
end;

need help to correct this query not work .

Posted 10-Jul-15 19:46pm

Praveen_Kumar Gupta

Add a Solution

Comments

Wendelius 11-Jul-15 1:50am

What is the problem with the query? Do you get an error? Does it return wrong results?

[no name] 11-Jul-15 2:04am

not execute, synatax error

Wendelius 11-Jul-15 1:52am

AT a glance the query looks like it's not going to work. What do you try to query? What rows should be selected?

[no name] 11-Jul-15 2:04am

when i need filtered data then this filter else return all data in csv format

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-10T20:18:00

I'm not sure about the logic you're trying to achieve but the WHERE clause is false.

SQL

where (case 
          when pname is null then 1 
          else emp_name 
       end ) in
       (case 
           when pname is null then 1 
           else (select emp_name 
                 from employee 
                 where emp_name like '%'||pname||'%') 
        end );

If you think about the statement, if the pname parameter is null you would basically have a condition like:

1 IN (1)

Also if the pname is not null this would result in peculiar statement.

From what I gather is that if pname is null you want to return each employee row. Otherwise you want to return only the rows having the provided pname. So perhaps something like:

SQL

WHERE emp_name LIKE '%'||pname||'%'
OR    COALESCE(pname, 'NON-EXISTENT') = 'NON-EXISTENT'

Oracle Query Conditional in clause

1 solution

Solution 1

Add your solution here

Preview 0

Oracle Query Conditional in clause

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us