Thinking out of the box

Recently a new BIN was installed in my company; this was supposed to solve some problems, but it also introduced new ones. The most egregious example that I could find is concerned with passing values from a procedural parameter to the procedure itself. If for example, I have a procedural parameter called PRT that is connected to the PART table, the code that the debugger develops for this when I run the procedure is near enough what one might expect:

INSERT INTO PART B SELECT * FROM PART A WHERE  A.PARTNAME 
LIKE '0957*';

Here the aliased table PART A comes from the procedural parameter. But if someone else runs the procedure, the following code is produced

INSERT INTO PART B SELECT * FROM PART A WHERE  A.PARTNAME 
='9057*' AND A.PARTNAME IN (SELECT PART.PARTNAME  FROM 
TEC_PART TEC_PART5 ?, PARTSPEC  , PARTDES  , PARTPRICE ?
(and another thirty lines of joins to other tables)

These extra joins cause the procedure to 'die' without completion. So how can this problem be overcome?

For this, a great deal of out of the box problem solving is required because the canonical solution has always worked. As it happens the internal SQL code is what gave me the clue to solving this challenge without the use of a procedural file parameter. I was working yesterday on a procedure that receives two procedure parameters based on PART where their type is LINE, not FILE, along with one 'regular' procedural parameter. Within the procedure, the internal part number is retrieved from the linked table with little difficulty; in the workaround, this procedural parameter is changed from type LINE to CHAR. I'll show below the original code and the workaround.

/* original version */
LINK PART TO :$.PRT;
ERRMSG 1 WHERE :RETVAL <= 0;
:OLDPART = 0;
SELECT PART, PARTNAME INTO :OLDPART, :OLDNAME
FROM PART
WHERE PART > 0;
UNLINK PART;
ERRMSG 2 WHERE :OLDPART = 0;
/* new version */
:OLDPART = 0;
SELECT PART, PARTNAME INTO :OLDPART, :OLDNAME
FROM PART
WHERE PARTNAME = :$.OLD;

But there is also a third procedural parameter linked to PART and this one is more complicated as it has to remain as a linked table and not a single part. There are several possible values that can be passed in the parameter: it can be empty, it can be *, it can be a specific part (e.g. 0926, if there is such a part) or a range (e.g. 0926*). How can this be handled?  The first two cases are relatively easy to handle after one makes the conceptual shift, but the third (or rather, the fourth) case is more difficult. My original code for this was as follows

GOTO 1 WHERE (:$.FAT = '') OR (:$.FAT = '*');
INSERT INTO PART
SELECT * FROM PART ORIG
WHERE ORIG.PARTNAME LIKE STRCAT (:$.FAT, '%');
LABEL 1;

In other words, don't link PART at all if :$.FAT is either empty or an asterisk. But this code is not legal syntax as LIKE cannot contain a variable. So what can be done? The solution is to replace LIKE with BETWEEN, where the lower limit will be the string passed in :$.FAT without the asterisk, and the upper limit will the the lower limit with ZZZ concatenated at the end. If :$.FAT is 0926*, then the lower limit will be 0926 and the upper limit 0926ZZZ - this covers all the parts in the same range as would 0926*.

GOTO 1 WHERE (:$.FAT = '') OR (:$.FAT = '*');
:N = STRINDEX (:$.FAT, '*', 1);
:A = SUBSTR (:$.FAT, 1, :N - 1);
:B = STRCAT (:A, 'ZZZZ');
SELECT SQL.TMPFILE INTO :FATHER FROM DUMMY;
LINK PART TO :FATHER;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO PART
SELECT * FROM PART ORIG
WHERE ORIG.PARTNAME BETWEEN :A AND :B;
LABEL 1;

I thought at some stage that it would be better to start the entire block with the STRINDEX function; whilst this works when :$.FAT is empty (returning 0), a dump file was created when :$.FAT contained a single asterisk, so this condition is checked as a normal string condition. After this snippet executes, PART will either be unlinked and so containing all the parts, or it will be linked and will contain only the chosen parts. 

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.

[Edit: it has been pointed out to me that this is an alternative method to a CHOOSE step. I am aware of this, but there are two reasons why I developed this technique:

1. For its intrinsic value. I've never seen this before and I certainly haven't seen the SQL
2. In the procedure where this is used, there are two such variables as well as several other standard parameters. This would have meant three separate screens that the user would have to execute and I don't like to overwhelm the user.

The single character bug returns

This morning I was tasked with retrieving the location of inventory within a specific warehouse. I wrote the following code that is part of a larger procedure that creates a warehouse transfer document.

:LOC = '0'; /* default value */
SELECT WAREHOUSES.LOCNAME INTO :LOC
FROM WAREHOUSES, WARHSBAL
WHERE WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.PART = :PART
AND WAREHOUSES.WARHSNAME = 'Main';

When the procedure containing this code was run, there was a complaint that location R does not exist for warehouse Main. This error was doubly confusing - at first, I thought that the R might refer to the part's type, which would make no sense whatsoever. When I ran the snippet in Windbi, the result was something like R.1.2, which was when I understood what was happening.

:LOC is initially set to the single character '0', for if the retrieval fails (for example, if the part has no inventory) then at least :LOC will have a value that is guaranteed to exist. Unfortunately, as I discovered several years ago¹, initialising a string with a single character confuses Priority who thinks that this is a character variable, not a string, and so saving further values into this variable will only save the first character.

The solution is to have an initialisation line at the beginning, like :LOC = '00000000' (I imagine that the length doesn't matter too much, as long as it's longer than one character. Once this line had been added, the addition to the procedure worked flawlessly. 

I notice that I wrote in the earlier blog that this issue had been fixed in version 21. I'm not sure with which version I was working today (it was for an external client), but it was web-based and probably not less than version 23.

Internal links
[1] 51

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'.

String length bug found

In a procedure that sends email, I had the following expression

:EMAIL = (:$.DBG = 1 ? 'noamn@testing.com' : :EMAIL);

The value of :EMAIL prior to this expression was sigall@somecompany.co.il - the length of this string is 24 characters, whereas the length of noamn@testing.com is 17 characters. Regardless of the value of :$.DBG, the resulting string would have a length of 17 characters, and so if :$.DBG = 0, there would be an attempt to send an email to the non-existing address sigall@somecompan; of course, this would fail.

As always, it took me quite some time to find out why the email address in the letter was being truncated but took only a minute to fix. If my address is assigned to a variable prior to evaluation, then the resulting value will have the correct length.

:EMAIL = 'sigall@somecompany.co.il';
:NOAM = 'noamn@testing.com';
:EMAIL = (:$.DBG = 1 ?  :NOAM : :EMAIL);

If :$.DBG = 0 then :EMAIL = 'sigall@somecompany.co.il'. If :$.DBG = 1 then :EMAIL = 'noamn@testing.com'.

How to crash the syntax checker

I wrote a procedure the other day in a rather hurried manner that was intended to round prices in a price list. The syntax checker crashed repeatedly, creating a dump file, when I checked this procedure, both in the Web interface and the Windows interface. I found what the offending statement was by the tedious process of removing as many lines as possible, then adding back individual parts until the program crashed again.

It was a 'simple' error - :QUANT = REALQUANT (1.0). The function REALQUANT expects to receive an integer for its input and outputs a real. In the above call, I am passing a real to the function that causes it to explode. Had I not been writing so fast, I would have noticed the error before checking the syntax as the expression was inside an INSERT INTO clause, and the expression was being saved in a integer field.

The opposite mistake, INTQUANT (1), gives the error 'Parameter for function must be of REAL type'. It's a shame that there's no error message for REALQUANT (1.0).

---

On a slightly different topic, I wanted to see whether the AI program CoPilot can help in writing procedures. I gave it a complete procedure - the one described above - and all CoPilot did was add obvious comments that are totally superfluous. I then asked whether CoPilot could improve the procedure - it introduced new control statements such as WHILE and ENDWHILE that would be great if they were allowed, but sadly no.

Asking CoPilot to write a procedure to give data about items in invoice lines was a waste of time - half of the garbage that CoPilot returned came straight from my original procedure (including linking GENERALLOAD and checking for errors after EXECUTE INTERFACE) and the original half was also garbage.

It seems at the moment that I am better at writing Priority procedures than this albeit free AI program.

In CoPilot's defence, I will note that originally it presented some code written for the Rest API: this code may be correct but I didn't look at it.

Learning something new every day (GROUP BY without an aggregation function)

I'm sure that we've all written procedures that send a report to all customers that ordered today (it doesn't have to be customers; it can be vendors or users in service calls or similar). I've always done this by means of a cursor such as

SELECT CUSTOMERS.CUSTNAME, COUNT (*)
FROM ORDERS, CUSTOMERS
WHERE ORDERS.CUST = CUSTOMERS.CUST
AND ORDERS.CURDATE = SQL.DATE8
GROUP BY 1;
 

I saw that someone had done something similar, but instead of using COUNT, he was using DISTINCT and instead of CUSTOMERS.CUSTNAME, a conditional expression was used, something like

SELECT DISTINCT (ORDERITEMS.ICURRENCY <> ORDERS.CURRENCY ? 
ORDERITEMS.ICURRENCY : ORDERS.CURRENCY),
...

I try to avoid using DISTINCT like the plague; to me it always smacks of lazy programming. One use that I do condone is if I want to know how many separate customers ordered today, as opposed to knowing how many orders each customer made today. I think that the above doesn't work properly because the field that DISTINCT is trying to filter is a conditional field whose value probably isn't known when DISTINCT does its work.

Today I discovered a new twist on the first query shown above: it turns out that the count is unnecessary (assuming of course that I only want a list of individual customers who ordered today). The following gives the desired result and should be faster as there is no need for counting. To my surprise, this syntax works in Priority.

SELECT CUSTOMERS.CUSTNAME
FROM ORDERS, CUSTOMERS
WHERE ORDERS.CUST = CUSTOMERS.CUST
AND ORDERS.CURDATE = SQL.DATE8
GROUP BY 1;

In other words, one can use 'GROUP BY' without an aggregation function (but not the other way around!).

How to include a backslash ('\') in a string

Someone wondered how they could convert a string holding a file name (such as 'Z:/ABC/DEF.TXT') into a file name that the file system would recognise ('Z:\ABC\DEF.TXT'). This turned out to be unexpectedly difficult, primarily because Priority regards the backslash (\) as an escape character and there is no real mechanism for obtaining or assigning a single character to a variable.

The first part - replacing the forward slashes with hyphens - was simple.

:PAR3 = 'Z:/ABC/DEF.TXT';
:SLASH = '-';
LABEL 10;
:PAR1 = STRPIECE (:PAR3, '/', 1, 1);
GOTO 20 WHERE :PAR1 = :PAR3;
:PAR2 = STRPIECE (:PAR3, '/', 2, 9);
:PAR3 = STRCAT (:PAR1, :SLASH, :PAR2);
LOOP 10;
LABEL 20;
/* at this stage, :PAR3 will be Z:-ABC-DEF.TXT */

I could output the string to a file then use the FILTER program to change the hyphens into blackslashes, but then the user would be faced with the problem of getting the string out of the file.

This morning, the answer hit me when I was doing something else entirely. The problem of the backslash is the same as the problem of the dollar sign, so the solution is the same: create a message (in this case, 11) whose text is simply \. The code now becomes

:PAR3 = 'Z:/ABC/DEF.TXT';
SELECT ENTMESSAGE ('$', 'P', 11) INTO :SLASH FROM DUMMY;
LABEL 10;
:PAR1 = STRPIECE (:PAR3, '/', 1, 1);
GOTO 20 WHERE :PAR1 = :PAR3;
:PAR2 = STRPIECE (:PAR3, '/', 2, 9);
:PAR3 = STRCAT (:PAR1, :SLASH, :PAR2);
LOOP 10;
LABEL 20;
/* at this stage, :PAR3 will be Z:\ABC\DEF.TXT */

Beware when using SQL.LINE

In my experience, there are two general cases of insertion into a table during a procedure: the table is normally one of the STACK tables or GENERALLOAD, but in certain cases can be another table, normally a private one that has been defined specially for the procedure. The identity of the table is not important in the cases that I am going to describe. The two types might be called 'explicit' and 'implicit', with reference to the key field of the table into which data will be inserted.

I am going to describe in general terms the copying of a customer order. This would use the GENERALLOAD table; the fields of the order header would go into a tuple whose value for RECORDTYPE would be '1' and whose LINE would be 1. The technique for copying the order lines depends on whether only the lines are being copied, or whether any sub-forms of the lines are being copied as well. In the first case (no sub-forms), one can simply write

  INSERT INTO GENERALLOAD (LINE, RECORDTYE, ...
  SELECT 1 + ORDERITEMS.LINE, '2', ...
  

If one were feeling adventurous, or there was no natural key for the sub-form, one could replace ORDERITEMS.LINE with SQL.LINE. What is important is that this number is incremented by one every time, as line 1 in GENERALLOAD holds the header line. This is what I would describe as 'implicit' inserting.

Should there be sub-forms, the data has to be entered by means of a cursor, where first line data is added then sub-form data. As there will no longer be any correspondence between GENERALLOAD.LINE and ORDERITEMS.LINE, one has to maintain a local variable (normally :LINE) whose value is incremented prior to every insert. This is 'explicit' inserting. At the same time, data for the sub-form could be inserted either implicitly or explicitly.

  :LINE = 1;
  INSERT INTO GENERALLOAD (LINE, RECORDTYE, ...
  SELECT :LINE, '1', {header data};
  DECLARE C1 CURSOR FOR
  SELECT ORDERITEMS.ORDI, ....
  OPEN C1;
  GOTO 200 WHERE :RETVAL <= 0;
  LABEL 100;
  FETCH C1 INTO :ORDI, ....
  GOTO 200 WHERE :RETVAL <= 0;
  :LINE = :LINE + 1;
  INSERT INTO GENERALLOAD (LINE, RECORDTYPE, ...
  VALUES (:LINE, '2', {line data} ...);
  /* sub-form */
  INSERT INTO GENERALLOAD (LINE, RECORDTYPE, ...
  SELECT :LINE + SQL.LINE, '3', {sub-form data} ...;
  LOOP 100;
  LABEL 200;
  CLOSE C1;
  EXECUTE INTERFACE ....
 

There is a deliberate mistake in the above code, but first let's think it through. The value of GENERALLOAD.LINE for header data will obviously be 1, and the value of this field for the first line's data will be 2 (as the variable :LINE is explicitly incremented). For the first line of the sub-form data, GENERALLOAD.LINE will be :LINE + SQL.LINE, i.e. 2 + 1, or 3. The second line will have GENERALLOAD.LINE = 4. This can be represented in the following table

 

GENERALLOAD.LINE	DATA
1	order header
2	first line of order
3	first line of sub-form for order line 1
4	second line of sub-form for order line 1

So far so good. For the second order line, :LINE will explicitly be incremented, so this line will have GENERALLOAD.LINE = 3 ... except for the fact that there is already a tuple with this key value in GENERALLOAD, and so the second line will not be inserted. What is missing is the following line:

SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;

This line should appear just before LOOP 100. As a result of this line, :LINE will have the value 4 after the insertion of the second sub-form line, and as this value is incremented prior to inserting the second order line, this line will have GENERALLOAD.LINE = 5.

One can generalise this: whenever one uses the construct 'INSERT INTO ... SELECT SQL.LINE', one must remember to increment SQL.LINE with a variable (such as :LINE or :MAX), then after the INSERT statement should come the statement that selects the maximum key number inserted so far into the above variable.

Such a simple heuristic, so easy to forget: if this variable (:LINE) is not used again, it seems that there is no point in extracting its value. For example, I have written many procedures that send a report by email: the procedure starts by defining some variables, then there is an insert statement into STACK4 (or similar) based on those variables, using SQL.LINE as the key field, then a report is executed passing STACK4 as its data. There is no loop and so there is no need to select the maximum value of KEY from STACK4. 

The problem rears its head when such code - which was written for a non-looping procedure - gets copied into a procedure that does have a loop. For example, I wrote a somewhat complicated procedure that collects BOM data from all the lines in a given order; this was then extended to work on several orders. Each order is selected via a cursor; local variables have to be reinitialised for each new order, but as the data are being inserted into the same STACK table, the key value should continue to increment. In other words, the maximum value of KEY should be extracted before the LOOP command that causes a new order to be selected.

This blog is of course being written because I fell foul of this heuristic. In this case, the procedure had to call an external program for calculating budget use; this program appears to work on one year's data at a time. As a result, I was forced to use the rather arcane structure of having a loop at the level of SQLI stages as shown below.

Stage	remarks
10	Set up variables
20	increment variables
30	external procedure
40	insert the data returned from the external procedure into a special table. At the end check the terminating condition and set :$.GO appropriately
50	GOTO 20 if the end has not been reached, 60 if it has been reached
60	cleanup

One can guess what the problem was: the code in stage 40 had originally been written for one year's data and used the 'INSERT INTO ... SELECT SQL.LINE' construct without having 'SELECT MAX (LINE)' at its end. When the procedure was run, it seemed to work. Let's say that the first year had 200 lines to be inserted, the second year 300 lines and the third year 100 lines. In this case, the 200 lines of the first year would get inserted without problem; the first 200 lines of the second year would not be inserted because SQL.LINE would return values that had already been inserted, but the final 100 lines (i.e. 201-300) would be inserted, giving the impression that the procedure worked for this year. Data from the final year would not be inserted at all.

It took me quite some time to figure out what the problem was; this was exacerbated by the facts that the procedure was working on a client's data (that are unfamiliar to me) and that the procedure was based on complicated code involving budgets (that too are somewhat unfamiliar to me). All the checks that I inserted (sorry for the inadvertent pun) showed the expected results, but somehow data was not being inserted into the table. I think that I wrote about a similar problem years ago: when data does not get inserted into a table, check its primary key.

It was less clear in this case because the loop construct was not within the same stage as the data insertion, but even so .... After 'INSERT INTO ... SELECT SQL.LINE', ALWAYS add 'SELECT MAX (LINE)' at the insert statement's end.

An SQL tip for comparisons

I want to describe a technique that I found a few days ago ('necessity is the mother of invention'), but I don't really know how to title it. I had a procedure that found work orders, but only those connected to customer orders. This requires the condition SERIAL.ORDI > 0. Someone else wanted the same report, but for work orders that are not connected to customer orders, namely SERIAL.ORDI = 0. I know how to use a flag in order to change a condition, but this always assumes that the comparison operator (>, =) is the same - but it isn't in this case.

I found a solution, although I am fairly certain that there are other ways of doing this. Let's say that :$.FLG = 'Y' if the user wants only work orders connected to customer orders. My solution converts the comparison operator into BETWEEN in the following manner:

  GOTO 1 WHERE :$.FLG = 'Y';
  :FROM = 0;
  :TO = 0;
  GOTO 2;
  LABEL 1;  
  :FROM = 1;
  :TO = 0;
  SELECT MAX (ORDI) INTO :TO FROM ORDERITEMS;
  LABEL 2;
  SELECT .......
  WHERE SERIAL.ORDI BETWEEN :FROM AND :TO
  ....
  

When :$.FLG <> 'Y', the users wants only work orders not connected, so FROM and TO will be zero. Otherwise the values are 1 and max (ordi) - guaranteed to work. Had the request been for work orders that might be connected to a customer order, then FROM would have been redundant: it would be 0 in both cases.

Syntax problem in 'clever dick' HTML documents

In this blog,  I showed a way to fool the HTML document generator by passing it one linked file, but then creating a new linked file and inserting into it only the records that one wants. I am in the process of upgrading our Priority version, and the program that checks procedures gave me an error message for this procedure: "Unresolved identifier TEST_WWWSHOWCUSTA.PAR". This is a mystifying message as the procedure works perfectly and of course PAR is a recognised identifier.

I discovered that the error came from this line, the first in the procedure:

:OLDPAR = :$.PAR;

This saves the value of the parameter holding the list of customers (or whatever) in the local variable 'oldpar'; internally the value of the parameter is the name of an external file somewhere on the server and so can be viewed as a regular string. If I commented out this line, the error message would disappear, although of course, the procedure wouldn't work. This led me to believe that the error message was probably a bug in the syntax checker: I was using syntax that the checker didn't recognise.

My colleague Yitzchok suggested an alternative (and a more SQL-like) syntax:

SELECT :$.PAR INTO :OLDPAR FROM DUMMY;

This has the same effect but also passes the syntax check. Once I discovered this, I quickly changed the three or four procedures that I had written using this 'replacing PAR' technique so that they too would not fail the syntax check. 

Introducing the Priority procedure cross referencer and fault analyser

 Over the months, I have collected various mistakes that I have made whilst programming procedures in Priority and that were not noted by the in-built syntax checker. I want to write a program that will check things that are not caught by the internal program, to supplement it and not replace it. Simply put, I want to write an external syntax checker that will check things like matched LINK/UNLINK pairs, uninitialised variables and a few other problems. I've been devoting a fair amount of thought as to how to store the data of such a program; traditional cross reference programs in Pascal used linked lists, and indeed I found such a program yesterday evening. But I would like to have a much more modern interface and use types such as stringlists and similar. A stringlist is ideal for storing what would have been an array of identifiers but isn't so useful when additional data regarding those identifiers is required.

I will no doubt continue to debate the subject in my mind until I commence coding; at the moment, my inclination is to take the old school cross referencer and adapt it to the Priority SQL syntax. This will be a complex task that would have to be done one way or another, so it's probably better to start with something that works so that I can concentrate on the syntax and not on how everything is stored. A cross referencer is a good idea anyway: it makes finding references to a variable much easier. I started writing a long program in Priority yesterday afternoon and finished writing and debugging this morning: this is 270 lines long which is fairly long but not too complicated. During the writing process, I moved pieces from place to place within the program (primarily moving non-variant operations out of loops, to be technical) and sometimes these edits slightly mangled the text. I discovered a new bug: a variable will always start with a colon (e.g. :DAYS); in the course of one of these edits and pastes, I had a variable named ::DAYS which is not the same as :DAYS. 

A cross referencer helps in finding variables that appear only once; this can mean that either the variable is superfluous as it is never used, or more problematic, it is a variable without value (as in the above case of ::DAYS). I wasted an hour yesterday on another procedure, trying to figure out why a value being saved in a variable was not being written later on. Eventually I saw the problem: the value was saved in :PARTCOST but was later accessed as :PARCOST. A cross referencer would find this immediately.

After spending more than a few hours over the weekend working on the cross-referencer, I have completed the first version.

As in the army, everything divides into three. For this program, the first stage is parsing the input file, then displaying the references and finally displaying the analysis. The first stage can also be split into three: the tokeniser, the lexical analysis and the storage. A token is a string extracted from a text file; for example, if the current line is 'select part, partname from part', then there are five tokens: 'select', 'part', 'partname', 'from' and again 'part'. In programming languages with regular syntax, the tokeniser is normally quite straight-forward, but it turns out that the procedural SQL language of Priority does not have regular syntax and cannot be considered to be context free.

Two examples of the ad hoc syntax: I want to note when a variable is initialised and when it is not. Initialisation can occur in one of two forms: either there is an equals sign after the token (e.g. :SEARCHNAME = '12345') or the keyword INTO precedes the token (e.g. SELECT DAY INTO :DAYS). These two opposite options (one prefix and one postfix, to use the technical terms) make it complicated to program. Another syntactic problem is the colon - :. Normally this serves to mark variables, e.g. :DAYS, but it can also be used to separate between two clauses in a ternary comparison (e.g. :DAYS < 7 ? 3 : 5). 

The correct tokenisation of table aliases (e.g. GENERALLOAD F1) took quite a bit of time.

Storage of the identifiers and their references is by means of a binary tree; this part was based on the cross referencer that I found a few days ago which was written in standard Pascal. The references are stored in a queue for each node. I added a few fields to these variable types in order to store further information: the type of identifier (variable, cursor, table) and the operation in progress at the reference (e.g. variable initialisation, opening a cursor, linking a table). This part was simple. Displaying the references was also fairly straight-forward.

The analysis part is dependent on the type of identifier: there are certain checks for variables, certain checks for cursors and certain checks for tables. I found a method to make these checks as stream-lined as possible.

I tested the program by running it alternately on a short test file into which at times included deliberate errors (so that I could check that the errors were being picked up) and on the file for the procedure that I wrote a few days ago. Every time I would look at the references, noting mistakes that had to be fixed. Now I'm 99% confident that I've correctly parsed the files and have correctly denoted variable initisalisation (this was very complicated). Running the finished program on my procedure finds three variables that were initialised and never used. These can be safely deleted from the procedure.

My next step is to publicise the program within a small community, inviting examples of procedures whose analysis appears to be wrong. Maybe there are other checks that need to be added.

Beware of the dollar sign (continued)

I have found a much simpler method of obtaining a naked dollar sign to be inserted within a string, using ENTMESSAGE. One defines a message number whose text is simply $. Here is the code which (to me, at least) is self explanatory.

:PAR1 = 'TEST@TEST.COM';
SELECT ENTMESSAGE ('$', 'P', 10) INTO :PAR2 FROM DUMMY; /* $ */
:PAR3 = STRCAT (STRPIECE (:PAR1, '@', 1, 1), :PAR2,
STRPIECE (:PAR1, '@', 2, 1));
WRNMSG 99;

As a result of these statements, PAR1 will be TEST@TEST.COM, PAR2 will be $, and PAR3 will be TEST$TEST.COM. Using ENTMESSAGE is a better method as it relies only on itself - it doesn't rely on a specific value in the CURRENCIES table (which might not exist) nor does it rely on an entry in a special constants table.

Note that the ENTMESSAGE statement uses '$' to denote 'the current procedure'.

Beware of the dollar sign!

At a company for which I am doing piecework programming, I was asked to fix a procedure that was giving an error message about being unable to create a file. I looked at the procedure (one which prints invoices); the problematic code was trying to create a string whose contents - basically the invoice number and customer email - would be displayed as a barcode in the final document.

Why is this problematic? For those who don't know, the character set that can be displayed in a barcode is severely limited to upper case characters, digits and a few characters such as * and $, but not @. Email addresses have the '@' character, so the original programmer had to find a way of replacing this with the '$' character.

In order to achieve this, the original programmer used a baroque set up of creating the initial string (with '@'), writing it to a file, then running the FILTER program three times (!) in order to get the file contents (i.e. the string) into the required format; one run replaced @ with $, one run turned the entire string into upper case; I don't know (nor care) what the third run did. This file was then loaded via an interface into a simple table from which the string was extracted and eventually displayed. Breathtaking in its ingenuity but totally misguided. To be fair, the procedure might have been written for a much earlier version of Priority in which certain functionality might be missing.

The actual problem that the client faced was that the intermediate files were being written to the root of disk C: which is of course a no-no. I simply changed the file directory and the procedure started working. But the sheer complication of this procedure irked me and I was sure that I could find a simpler way of creating the required string.

Here is my first attempt:

SELECT TOUPPER (CUSTOMERSA.EMAIL) INTO :EMAIL
FROM CUSTOMERSA, INVOICES
WHERE CUSTOMERSA.CUST = INVOICES.CUST
AND INVOICES.IV = :$.IV;
SELECT STRCAT ('*E', IVNUM, 
STRPIECE (:EMAIL, '@', 1, 1), '$',
STRPIECE (:EMAIL, '@', 2, 1), '*') INTO :$.BC
FROM INVOICES
WHERE IV = :$.IV;

If the invoice number were IV200001 and the email test@microsoft.com, the resulting string would be expected to be '*EIV200001TEST$MICROSOFT.COM*'. Unfortunately this would not be the result. Using the naked dollar sign ('$') causes the preprocessor in the parser to replace this with the name of the procedure, resulting in something like 

'*EIV200001TESTTEST_WWWSHOWCIV2MICROSOFT.COM*'! This is normally a good thing as it enables one to pass the current procedure name to the ENTMESSAGE function as $, without denoting the name - as a result, the same code can be copy/pasted between procedures without problem.

A method of obtaining the naked dollar sign without using the naked dollar sign is required! After some lateral thinking, I came up with the following, but unfortunately it too inserts the first letter of the procedure's name instead of the dollar sign.

SELECT TOUPPER (CUSTOMERSA.EMAIL) INTO :EMAIL
FROM CUSTOMERSA, INVOICES
WHERE CUSTOMERSA.CUST = INVOICES.CUST
AND INVOICES.IV = :$.IV;
:DOLLAR = '1$1';
SELECT STRCAT ('*E', IVNUM, 
STRPIECE (:EMAIL, '@', 1, 1), STRIND (:DOLLAR, 2, 1),
STRPIECE (:EMAIL, '@', 2, 1), '*') INTO :$.BC
FROM INVOICES
WHERE IV = :$.IV;

Incidentally, I don't see the above as being programming in Priority; it's got nothing to do with working with a database engine. Instead, it's more general programming in the context of the Priority programming language, which isn't something that can be easily taught. One has to remember that in a procedure, '$' is going to be expanded into the procedure's name.

I finally figured out how to solve the problem - use the code of currency -2, which is ... $ (at least, in companies where the default currency is NIS; for companies where the default currency is dollars, the number is -1). The below works in a test procedure on my server, but I will have to implement it fully on the client's server in order to be sure.

SELECT TOUPPER (CUSTOMERSA.EMAIL) INTO :EMAIL
FROM CUSTOMERSA, INVOICES
WHERE CUSTOMERSA.CUST = INVOICES.CUST
AND INVOICES.IV = :$.IV;
SELECT STRCAT ('*E', INVOICES.IVNUM, 
STRPIECE (:EMAIL, '@', 1, 1), CURRENCIES.CODE,
STRPIECE (:EMAIL, '@', 2, 1), '*') INTO :$.BC
FROM INVOICES, CURRENCIES
WHERE INVOICES.IV = :$.IV
AND CURRENCIES.CURRENCY = -2;

The above code did not work on the client's server as they have defined the code of currency -2 to be USD. Tired of knocking my head against a brick wall, I swiftly defined a personal table of constants, defined a constant with the value '$' then inserted the appropriate code into the procedure. This finally works!!

Don't be a miser with brackets

In reports, there are three very useful 'group functions': T, S and B, that can be used on columns containing numerical data. T causes the total of the column to be presented at the end of the report, S causes sub-totals to be presented and B causes both sub-totals and totals to be presented. Unless one is displaying oranges and apples in the same column, B is normally the preferred option.

One can also use these functions on calculated columns; for example, I have been working on a report which displays in one column expected costs, in another column the actual costs and in the third column the ratio of actual to expected costs. 

Let's say that the report looks like this

Expense	expected	actual	% ratio
Gas	160	170	106.25
Electricity	188	135	71.81
Water	207	197	95.17
Totals	555	502	90.45

In order to achieve this, the definition of '% ratio' is (in words) 100.0 times the actual cost divided by the expected cost. Assuming that 'expected' has a column number #100 and 'actual' has a column number #110, the definition of column 120 will be 100.0 * #110 / #100. The first two columns should have the group function B (or T) in order to display their total at the end, and the ratio should have the group function b (or t). 

Sometimes this works, sometimes it doesn't.

This simple version might well have worked, but I had to change it in order to prevent division by zero; let's say that there are actual costs that were not budgeted. In such cases, Priority displays 100 * #110, which is completely wrong! There should be a condition that if expected = 0, then the result is zero, else it's what the formula calculated. On in Priority-speak, #100 = 0 ? 0 : 100.0 * #110 / #100.

This formula certainly handles division by zero properly and also displays the correct ratio in each row, but now the total percentage ratio is no longer 90.45 but 273.23 - the sum of the percentages in each row, and not the percentage of the total!

I know that group function b works because I have a report that displays the total percentage correctly. So why doesn't it work here? After a great deal of unsuccessful magic incantations, I was forced to call in someone else. She too did not know the reason off-hand, but she compared the formula here to the successful formula in another report, and noticed that here I was using the ternary comparison expression (taken from C; read it as 'if #100 is zero then return 0 else return 100.0 * #110 / #100). 

It turns out that the parser in Priority is very temperamental when it comes to the order of evaluation of expressions and especially the ternary expression; the parser requires a great deal of help which one supplies by means of brackets. When these are applied liberally, the 'b' group function is finally honoured in the totals line: (#100 = 0 ? 0 : (100.0 * ( #110 / #100)).

The use of brackets is somewhat inconsistent in Priority and reminds me of the UNLINK command: one can often get away without using it, but sometimes it is essential and so one should always use it. Apparently the same as with brackets: don't be a miser with them!

Also it seems that one has to use the row numbers (#100, etc) instead of actual row fields (e.g. STACK4.REALDATA).

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.