Quite often I am faced with the problem of handling boolean fields in an SQL step; for example, if I want to write a query that has as one of its parameters the field ORDERITEMS.CLOSED, until now I have only been able to choose either lines where CLOSED = 'C' or ignore the field altogether. There is no way of choosing lines that are not closed.
... AND ORDERITEMS.CLOSED = (:$.FLG = 'Y' ? 'C' : ORDERITEMS.CLOSED) ...
The problem is that the parameter FLG can either be marked or unmarked, using the subform of the parameter to mark it of type Y, thus creating a checkbox. The equals sign that is displayed when running the procedure cannot be changed. So how can one handle the three different possibilites: closed, not closed, ignore?
Then in the query one has to use syntax that is legal but unusual:
SELECT LINE FROM ORDERITEMS WHERE ((:$.FLG = 1 AND CLOSED = 'C') OR (:$.FLG = 2 AND CLOSED <> 'C) OR (:$.FLG = 3 AND CLOSED = CLOSED)) ...
This passes the syntax check and even works! The third condition probably can be condensed to :$.FLG = 3.
[Edit: it has been pointed out to me that this is an alternative method to a CHOOSE step. I am aware of this, but there are two reasons why I developed this technique:
- For its intrinsic value. I've never seen this before and I certainly haven't seen the SQL
- In the procedure where this is used, there are two such variables as well as several other standard parameters. This would have meant three separate screens that the user would have to execute and I don't like to overwhelm the user.
