Getting a value from the last line in a table

I've often been in the situation where I want to get some value from the (currently) last line in a table. For example, if I want to get the customer number of the last order entered into the system. I would do this by means of two statements:

SELECT MAX (ORD) INTO :MORD
FROM ORDERS;
SELECT CUST FROM ORDERS
WHERE ORD = :MORD;

In case anyone was wondering, the following is illegal syntax

SELECT CUST FROM ORDERS
WHERE ORD = MAX (ORD);

But it turns out that there is a way of getting the desired value in one statement, although to be fair, the statement includes a subquery, albeit with special syntax. Below are two statements; the first works in Firebird but not in Priority SQL whereas the second works in both Priority and Firebird, although the Firebird query is very slow.

/* this gives an 'ambigous column name CUST' error in Priority */
SELECT O1.CUST   
FROM ORDERS O1
WHERE O1.ORD = (SELECT MAX (O2.ORD) FROM ORDERS O2);
/* this works in Priority */
SELECT O1.CUST   
FROM ORDERS O1
WHERE O1.ORD IN (SELECT MAX (O2.ORD) FROM ORDERS O2);

Something else to be filed under 'Learn something new every day'.

Refresh parameters for TTS task

My company has 260 tasks defined to run automatically by means of the task scheduler (TTS); the vast majority of these tasks are procedures that look for specific situations (e.g. open orders that should have been supplied) and assuming that there is output, send the report by email to a user group. These procedures have their parameters defined within their code. 

One procedure - not mine - that does not have its parameters defined internally is MRP. This procedure can of course be run manually, and there are over ten parameters that the user can change, not including several system constants that generally do not change. In order to run MRP successfully by the TTS, this task has an entry in the field 'run according to the input of user'. I have always understood that an automatic invocation of MRP will run using the parameters of a given user, and that if I want to change the parameters for the automatic run, then first I would have to run MRP manually and in doing so, change the parameters to their new values. Or so I thought.

Until now, the value of the parameter 'open work orders until' has been 'the end of the next quarter'; this currently would be 30/09/25. As unusually we have some orders with a supply date in October, no work orders were being created for these lines. The production manager wanted to know the reason for this; I explained, and we agreed that the value of this parameter would be changed to 'the end of the next year', at the moment 31/12/26. I ran MRP manually, entering this value, and indeed work orders were created for those orders with an October supply date from this run. 

I was thus surprised to see that in the next automatic run of MRP, the 'open work orders until' parameter had reverted to 30/09/25. My understanding of how to change parameters for automatic invocation was flawed. It turns out that there is a simple, but rarely used, fix for this situation: one can run a procedure (by direct invocation) called TTSUSERINPUT, 'Refresh Task Input by User'. It transpires that the task has its own set of parameters and these are updated from the user's values by means of this procedure. And indeed, the next automatic run of MRP after running this procedure was run according to the updated parameters.

File under  'Learn something new every day' ....

An important change in version 24 regarding the status log of orders

I have written many procedures and reports that look at an order's status log; all these procedures look at the ORDSTATUSLOG table. A client wrote to me a few days ago and told me that one of these procedures (that is run automatically by the TTS) had stopped working. I checked this table and saw that the last entry was almost one month ago, but that changes in order status were still being reported. My unofficial conclusion was that I should no longer reference the ORDSTATUSLOG table but rather TODOLIST.

This table holds data from many different tables, so accessing the required data is slightly more complicated than what used to be. Here is an example of the queries that I used to write in order to obtain all the orders that were approved 'yesterday'

SELECT ORDERS.ORDNAME
FROM ORDERS, ORDSTATUSLOG
WHERE ORDERS.ORD = ORDSTATUSLOG.ORD
AND ORDSTATUSLOG.ORDSTATUS = -2
AND ORDSTATUSLOG.UDATE BETWEEN SQL.DATE8 - 24:00 AND SQL.DATE8 -1
...

This now becomes

SELECT ORDERS.ORDNAME
FROM ORDERS, TODOLIST
WHERE ORDERS.ORD = TODOLIST.IV
AND TODOLIST.TYPE = 'O'
AND TODOLIST.DOCSTATUS = 135
AND TODOLIST..UDATE BETWEEN SQL.DATE8 - 24:00 AND SQL.DATE8 -1
...

In belated recognition, the client sent me an extract from the Version 24 release notes that sort-of documented this change: they note that there will be no new tuples in ORDSTATUSLOG but don't say where the log will continue to be maintained, presumably because status changes had always been logged in TODOLIST.

Writing multi-environment procedures

I have been tasked a few times to write a procedure that iterates over all the active environments and saves data in a special table that is going to be accessed by an API. There are some special gotchas that need to be overcome; the following will not work

DELETE FROM MYTABLE;
SELECT SQL.ENV INTO :HOME FROM DUMMY;
:LINE = 0;
DECLARE C1 CURSOR FOR
SELECT DNAME
FROM ENVIRONMENTA
WHERE ACTIVE = 'Y'
AND DNAME <> '';
OPEN C1;
GOTO 300 WHERE :RETVAL <= 0;
LABEL 100;
FETCH C1 INTO :NAME;
GOTO 200 WHERE :RETVAL <= 0;
ENV :NAME;
INSERT INTO MYTABLE (.................)
;
LOOP 100;
LABEL 200;
CLOSE C1;
LABEL 300;
ENV :HOME;

Presumably there is a form based on table MYTABLE; initially all the data is wiped, then the procedure iterates through the environments and saves whatever data need to be saved. At the end, the procedure returns to the initial environment (:HOME) and presumably the data is displayed in the appropriate screen. Unfortunately, as I noted before the code, this doesn't work: the MYTABLE table will have different instances in each environment, or in other words, MYTABLE in environment A is not the same as MYTABLE in environment B. The following will work

SELECT SQL.TMPFILE INTO :ROL FROM DUMMY;
LINK MYTABLE TO :ROL;
SELECT SQL.ENV INTO :HOME FROM DUMMY;
...
LABEL 300;
ENV :HOME;
DELETE FROM MYTABLE ORIG;
INSERT INTO MYTABLE ORIG
SELECT * FROM MYTABLE; /* the linked table */
UNLINK AND REMOVE MYTABLE;