PeopleSoft Learning

ALL, SOME  and ANY :- These operators are not widely used in the SQL, these keywords exists in both Oracle and MS SQL. These keywords can be very useful in developing many peoplesoft realated query. It makes the query simple to write and understand.

Eg:- If you want the list of employees who are terminated in all the assignments (employee records).

 1) Select EMPLID from PS_PERSON A
Where 'I' = ALL (Select HR_STATUS from PS_CURRENT_JOB J
Where J.EMPLID = A.EMPLID )

2) Select EMPLID from PS_PERSON A
Where 'A' <> ANY (Select HR_STATUS from PS_CURRENT_JOB J
Where J.EMPLID = A.EMPLID )

ANY or SOME: Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows.
ALL: Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, < , <=, >=. Evaluates to TRUE if the query returns no rows.

It is doesn't do anything new, it is just user friendly wrap keywords. The optimizer expands a condition that uses the ALL comparison operator followed by a parenthesized list of values into an equivalent condition that uses equality comparison operators and AND logical operators.The optimizer transforms a condition that uses the ANY or SOME operator followed by a sub query into a condition containing the EXISTS operator and a correlated sub query.

Due to this behaviour of the optimizer in Oracle SQL, we need to analyze sql for the performance before we take decision to use it in the production. These constructs have been useful for me in doing the data analysis and creating the quick query to get list of the employee based on certain condition.