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.