asked
SQL Server 2005 - Expression in ANY Clause.
Hi, Is there any way of writing the following SQL without having to use a subquery or IN clause in Transact-SQL: SELECT EMPLID, NAME FROM PS_EMPLOYEES WHERE EMPLID = ANY(SELECT EMPLID FROM PS_PERSON WHERE EMPLID IN ('411350', '411354')); In Oracle this works: SELECT EMPLID, NAME FROM PS_EMPLOYEES WHERE EMPLID = ANY('411350', '411354'); This is for Peoplesoft Query Tool that has limitations and wondering if anyone has any suggestions. Thanks, Sesh.
You've already done it with your subselect - using the `IN` clause - so why can't you use the `IN` clause? Why bother with the subselect, when the fields are the same SELECT EMPLID, NAME FROM PS_EMPLOYEES WHERE EMPLID IN ('411350', '411354'); is the same as SELECT EMPLID, NAME FROM PS_EMPLOYEES WHERE (EMPLID ='411350' OR EMPLID ='411354'); or even SELECT EMPLID, NAME FROM PS_EMPLOYEES WHERE EMPLID = ANY (SELECT '411350' as EMPLID UNION ALL SELECT '411354');
Thank you for the quick response. The limitation is with Peoplesoft Query Tool - if we use the IN Clause then each EMPLID needs to be entered separately instead of using an expression where the list of employees can be cut and paste. The users have 50 to 60 values that need to be entered and it is a lot of work - maybe I should ask this on a Peoplesoft forum. Thanks, Sesh.
Well, if you have to keep it as a subclause, you could always do: SELECT EMPLID, NAME FROM PS_EMPLOYEES WHERE EMPLID IN (SELECT EMPLID FROM PS_PERSON WHERE EMPLID IN ('411350', '411354')) Or am I missing something?
