I just read the OpenEdge PANS mail for today, which details the following bug: "LOOKUP in WHERE clause of dynamic query does not filter results with DataServer for Oracle" (http://knowledgebase.progress.com/articles/Article/LOOKUP-in-WHERE-clause-of-dynamic-query-does-not-filter-results-with-DataServer-for-Oracle?popup=true)
It contains the following query, which doesn't perform as expected with Oracle Dataserver:
FOR EACH employee FIELDS (empnum firstname lastname deptcode) NO-LOCK
WHERE LOOKUP(employee.deptcode, '100,200,300,400') > 0,....
Besides the fact that there is a bug, shouldn't Progress mention that it is a very bad idea to use a function in a query, especially when it's on the left side of a comparison?
A query will never use the index on deptcode if written like this!
Better would be to add a temp-table containing all valid deptcodes to the query:
FOR EACH ttDepartment
EACH employee FIELDS(...) NO-LOCK WHERE employee.deptcode = ttDepartment.deptcode,....
Or iterate a list when the number of departments is limited, and adding a temp-table would be overhead.
DO counter = 1 to NUM-ENTRIES(departmentList):
ASSIGN deptcode = ENTRY(counter, departmentList).
FOR EACH employee FIELDS(...) NO-LOCK WHERE employee.deptcode = deptcode,...
So instead of suggesting to add a NO-JOIN-BY-SQLDB query tuning hint, it would have been better to rewrite this query altogether...
Hi Lieven,
We can certainly add a note on that to the article mentioning best practices.
As to the workaround, re-writing the query may be the better option in an ideal world but it's not always feasible, especially when the construct in question has already been used extensively in an existing product and deadlines are short. NO-JOIN-BY-SQLDB mitigates the issue for the specific query and requires only a minor code change.
And for your reference, you can leave comments on articles by using the Feedback section at the bottom of the page.
Hi Rob,
Sure, but there are more people reading this article then there are experiencing this issues (I hope), so the warning not to use LOOKUP (or any other function for that matter) in a query (certainly at the left side of a comparison) could be useful for many...
I've added my feedback to the article (I didn't realize clicking "No" would allow me to add some comments)
Thanks Lieven. We do understand - if we can get the message out sooner rather than later the situation should be avoidable. Have picked up your article feedback and will action it in the coming days.