APPLY xxx WHERE SUBSTR(x,1,1) doesn't work

[irishdragon]

I'm doing an MLOAD with a delimited file. The delimiter is '|'.

On the first field (SPID_FL), I need to look at the first byte and only apply the record if this byte is '1'. This one byte has no '|' after it, so it's considered part of a larger field. See example.

10988|216-426-9210|39780555
^
|- I want to look at this byte, with no '|' following.

For some strange reason, the SUBSTR function doesn't work with an APPLY WHERE. If I don't use the SUBSTR, the APPLY WHERE works fine.

Code Exerpt:

.LAYOUT INREC;
.FIELD SPID_FL * VARCHAR(5);
.FIELD TN * VARCHAR(12);

...

.DML LABEL I_TATD820;

/* SEE DOCUMENT ABOVE */
INSERT ASKMEU.VATD820
( CNTLCARD_VERSION_SEQ_NBR = 1
, SPID = SUBSTRSPID_FL,2,4)
, TN =: TN

...

.IMPORT INFILE DATAIN
FORMAT VARTEXT
LAYOUT INREC
APPLY I_TATD820 WHERE SUBSTR(SPID_FL,1,1) = '1';

.END MLOAD;
.LOGOFF &SYSRC;
Here's the error:

APPLY I_TATD820 WHERE SUBSTR(SPID_FL,1,1) = '1';
13:51:23 UTY0006 Input error in the IMPORT command at position 151: "(SPID_FL,1,1)"

 

[dnoeth]

MLoad doen't support SQL syntax like SUBSTRING, but you got luck, that it's only the first char:

APPLY I_TATD820 WHERE SPID_FL >= '1' AND SPID_FL < '2';

Dieter