Dawn Dawn - 5 years ago 249
SQL Question

comparing using multiple arguments in the Lower/Upper function

I am trying to implement a query like below

SELECT *
FROM emp
WHERE LOWER (ename) IN LOWER ('A', 'b', 'C', 'd','eF','GG','Hh');


but getting error

ORA-00909: invalid number of arguments.


Is there any specific function by which I can compare multiple arguments in the 'IN' clause having lower case?

Answer Source

@Muhammad Muazzam has it right, but if for some reason you really need to user LOWER on the right hand side with a list of values then you could do this:

select ename from emp
where lower(ename) in 
(select lower(column_value) 
 from   table(SYS.KU$_VCNT('A','B','C','D'))
);

SYS.KU$_VCNT is a table of VARCHAR2(4000) type that should already exist in your database.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download