I need to allow my users to type in a list of names and return the names that are not in a table.
1. User types [ Tom Joe Bob MIKE ] into a Text Area item named :P2_NAME_CHECK
2. A table named CUST contains two rows with TOM and Bob in the
CUST_NAME column.
3. A report returns two rows JOE and MIKE.
Usually, it is oposite. You want to show the list of users that match the criteria.
The solution for this problem is quite easy if you know how to use pipelined functions. This is a case where the pipelined functions shine. I created an example in my Demo Application showing how this can be done.
Basically, the pipelined function will create a row for all substrings returning no rows from the emp table, showing those substrings as rows.
Denes
ReplyDeleteThis method executes a single row SQL query for each name in your list. A better way (especially if your list could be large) would be to use a SQL MINUS operation:
SELECT COLUMN_VALUE not_exists
FROM TABLE (get_existing_array (:p172_employee_name))
MINUS
SELECT lower(ename) FROM EMP;
Modify get_not_existing_array to get_existing_array so it can return the entire (lowercased) original list as a pipeline function.
In some cases it may be worth using a CARDINALITY hint to make sure the optimizer know how many rows to expect from the function.
Regards Nigel
Nigel,
ReplyDeleteThanks. You are right.
Denes