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.

No comments:
Post a Comment