Priority: LIKE cannot accept a variable

I wanted to write a form trigger which is dependent on the value of a certain field (the number of a previous order). Unfortunately, this field is not bound but rather a simple text field, which means that instead of it holding values like KL191234 (this would be the previous order number), it holds values like 1234, 191234 or even 1234/5. This is problematic, but one evening the answer came to me: use the standard SQL keyword LIKE, where the order number is preceded by *. Thus KL191234 will be matched by *1234 or *191234 (but not *1234/5). On this basis I wrote the following code, where TEST_PREVORDER holds the value of the previous order

:PREVORD = 0;
SELECT ORD INTO :PREVORD
FROM ORDERS
WHERE ORDNAME LIKE STRCAT ('*', :$.TEST_PREVORDER)
AND CUST = :$.CUST;
GOTO 99 WHERE :RETVAL <= 0;

The syntax checker told me that there was an error with STRCAT. In order to combat this, I placed the STRCAT function before the query, assigning its value to a variable, then used this variable in the query. This did not help matters.

Eventually I realised what the problem was - LIKE (at least, in its Priority definition) cannot accept a variable as its parameter; it has to be a 'naked' string like '*T' or 'T*'. As usual, this doesn't seem to be defined anywhere, but I discovered that I had commented on this a few years ago (not on this blog).

So add this to the growing list of improvements for Priority SQL. The syntax checker

1. should check that every cursor which is opened is also closed.
2. should check that every LINK has a matched UNLINK (this is probably very difficult)
3. should allow LIKE to take a variable as its parameter

The final code became

/* Stupid code required because LIKE cannot accept a variable as a
parameter */
:TPO = :$.TEST_PREVORDER;
:TLEN = STRLEN (:TPO);
GOTO 4 WHERE :TLEN = 4;
GOTO 6 WHERE :TLEN = 6;
GOTO 8 WHERE :TLEN = 8;
GOTO 99;
LABEL 4;
:TPO = STRCAT (ITOA (YEAR (SQL.DATE8) MOD 100), :TPO);
LABEL 6;
:TPO = STRCAT ('KL', :TPO);
LABEL 8;
:PREVORD = 0;
SELECT ORD INTO :PREVORD
FROM ORDERS
WHERE ORDNAME = :TPO
AND CUST = :$.CUST;
GOTO 99 WHERE :RETVAL <= 0;

Note that the code 'falls through' the labels: if TPO is '1234', then the code will jump to label 4, where TPO becomes '191234'. Then the code for length 6 executes: TPO becomes KL191234. Then the code for length 8 executes - which is the real code. In other cases, there would be another GOTO on the line before 'LABEL 6' but here it is not required.