I discovered the same problem, and have posted a screenshot explaining it here: http://bit.ly/1jasYHc
APEX (4.2.1.00.08) clearly lets you add the WHERE clause and error message. Neither have any effect. The loader seems to ignore the WHERE clause. When the lookup validation fails, you only get a message in the loader page that the lookup couldn't be found, without identifying the column(s) that failed, or presenting the error message(s).
There is no validation on the WHERE clause when you enter it, no indication whether to include the word "where" or not in the text box, and no failure at run time if you refer to columns that don't exist.
Unlike the WHERE clause, if you put in column aliases on the loader page, those work.
Hi Karuna / Skip,
Sorry for taking long to get back to you. These two issues are known and will be fixed by coming release.
- Where clause, was introduced and then replaced by the use of three columns for data lookup, (to get DEPNO , you would be able to pass DNAME, LOC, etc ). in 4.2 where clause was removed from other pages but stayed at 1802 by mistake. In coming release we are bringing back this where clause because there have been situation where this would be still valid.
- Error Message, this is again part of data loading that needed some improvement, we dont only want to display a generic error message for data loading lookup / transformation rules (in case data loading fails) but we would like to have details about the specific error as well. There is an improvement in coming release and this column will / will not be used depending on choices of the developer.
(There is bug 18390150 to track these changes)