If Statements Using SQL

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 realize 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...