Filtering on non-key fields

Nearly five years (!) have passed since I last wrote on this topic (then called 'zero values in Priority') on the other blog. I will explain again: normally, when one adds parameters to procedures, the parameter is based on a field that is guaranteed to have a value (e.g. customer number in an order, order type or similar). But it often happens that one wants a parameter on a field that is not guaranteed to have a value - today's blog will discuss filtering customers by their 'customer type' field that is not mandatory.

The original post showed how to filter successfully when a cursor is used, but I never found a satisfactory solution to filtering when tuples are saved using the 'insert/select' syntax. Looking at the old code, an answer popped into my head.

The first part is the same as ever:

:LINKED = :UNLINKED = 0;
SELECT COUNT (*) INTO :UNLINKED FROM CTYPE;
LINK CTYPE TO :$.CTP;
ERRMSG 1 WHERE :RETVAL <= 0;
SELECT COUNT (*) INTO :LINKED FROM CTYPE;

But the way that this is used is different

INSERT INTO STACK4 (KEY, INTDATA, REALDATA)
SELECT ORDERS.CUST, CUSTOMERS.CTYPE, SUM (ORDERS.DISPRICE)
FROM ORDERS, CPROFTYPES, BRANCHES, CUSTOMERS, CTYPE
WHERE ORDERS.ORDTYPE = CPROFTYPES.CPROFTYPE
AND ORDERS.BRANCH = BRANCHES.BRANCH
AND ORDERS.CUST = CUSTOMERS.CUST
AND CUSTOMERS.CTYPE = CTYPE.CTYPE
AND ORDERS.ORDSTATUS <> -6
AND ORDERS.CURDATE BETWEEN :$.FDT AND :$.TDT
GROUP BY 1, 2
HAVING SUM (ORDERS.DISPRICE) >= :$.NUM;
GOTO 1 WHERE :LINKED = :UNLINKED;
/* The user requested specific values of CTYPE, so remove from
STACK4 any tuples with intdata (ie ctype) = 0 */
DELETE FROM STACK4 WHERE INTDATA = 0;
LABEL 1;

In other words: collect and store in stack4 all the data as one would normally do. If the linked CTYPE table contains all the values, then all the suitable customers are selected: nothing new here. If only certain values are chosen in CTYPE, then all the customers with those values along with the customers with no value (i.e. CUSTOMER.CTYPE = 0) are chosen (selected).

After the insertion statement has finished, there is a check to see whether only specific values of CTYPE were chosen, and if so, then all tuples with the value 0 are deleted from the stack.