When building list windows for users you often want to allow them to
specify search criteria. If the search criteria are simple and only have a single field or
all fields must be entered than a simple WHERE clause with retrieval
arguments will do the job. For very complicated arguments with multiple selections you
will have to resort to dynamically altering the SQL behind the datawindow. But you may not
realise that if you do not require multiple selections for a single field then with some
clever SQL coding you can avoid time consuming dynamic SQL.
For example if we were building a selection window for employees, you may want to allow
the user to search based on employee number, employee name, Address or any combination of
the three. We can achieve this by declaring three retrieval arguments of the correct
datatypes. In your retrieve script on the window you would have designed the arguments
input criteria using a datawindow! so you could select the empty field is null option of
the edit control. If you have not and shame on you then if the field is empty you will
need to manually set it to null.
Then in the SQL you would code for the NULL values:
SELECT emp_no, emp_name, emp_addr1, emp_telno
FROM employee
WHERE ( emp_no = :al_emp_no OR :al_emp_no IS NULL )
AND ( emp_name = :as_emp_name OR :as_emp_name IS NULL )
AND ( emp_addr1 = :as_emp_addr OR :as_emp_addr1 IS NULL )
You would also want to concatenate '%' on to the end of the strings to allow for
pattern matching and convert both sides of the comparison to Lower case to make it more
user friendly, you would convert the retrieval argument to lower case once in Powerscript
and pass it to the datawindow:
( Lower( emp_name ) LIKE :as_emp_name...
|