Wednesday, 15 July 2026

Ternary logic in procedures

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?


The key to doing this is first to record three different but consecutive messages in the 'procedure messages' subform, eg 1 = 'only Y', 2 = 'only not Y', 3 = 'couldn't care'. Then a parameter has to be defined of type INT and in the parameter extension subform defined as type C, where the message numbers are from 1 to 3.

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.