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
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
- should check that every cursor which is opened is also closed.
- should check that every LINK has a matched UNLINK (this is probably very difficult)
- should allow LIKE to take a variable as its parameter
/* 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.