Oracle Query Conditional in子句

问题描述

我在db中创建一个函数，返回我的员工姓名，

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

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 .

推荐答案

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 );

如果考虑一下这个语句，如果pname参数为null，你基本上会遇到这样的情况：

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

1 IN (1)

如果pname不为空，这将导致特殊声明。



从我收集到的是，如果pname为null，您希望返回每个员工行。否则，您只想返回具有提供的pname的行。所以可能是这样的：

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:

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

这篇关于Oracle Query Conditional in子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！