Using a result of a substring for the length value of another substring

I am sure that the person who asked me if this was possible is not the first person to have tried, and was unable to get it to "work".

They had been presented with a "flat" file where the first two characters denoted the length of the key value. What they need to do was to extract the variable length key into another value they could use.

The file was like this:

DATA ---------------------------------- 10<-- 10 -->XXXXXXXXXXXXXXXXXXX... 05<-5->XXXXXXXXXXXXXXXXXXXXXXXX... 20<------- 20 ------->XXXXXXXXX...

It is easy to extract the first two characters from DATA using the following SQL Select statement:

01 SELECT SUBSTR(DATA,1,2) AS "Length", 02 DATA 03 FROM TESTFILE ;

Which returns:

Length DATA ------ ---------------------------------- 10 10<-- 10 -->XXXXXXXXXXXXXXXXXXX... 05 05<-5->XXXXXXXXXXXXXXXXXXXXXXXX... 20 20<------- 20 ------->XXXXXXXXX...

Let me try to use the code I used to generate the "Length" to substring the key value from DATA:

01 SELECT SUBSTR(DATA,1,2) AS "Length", 02 SUBSTR(DATA,3,SUBSTR(DATA,1,2)) AS "What I want", 03 DATA 04 FROM TESTFILE ;

Line 2: You can see the nested substring as the third part of the first substring.

Alas, this statement errors with the following message. As I am using ACS's "Run SQL Scripts" the message appears in the results/messages window.

SQL State: 42815 Vendor Code: -171 Message: [SQL0171] Argument 3 of function SUBSTR not valid. Cause . . . . . : The data type, length, or value of argument 3 of function SUBSTR specified is not valid.

This is not a surprise as substring returns a character result. Let me convert the returned character value to a number by using the TO_NUMBER built in function:

01 SELECT TO_NUMBER(SUBSTR(DATA,1,2)) AS "Length", 02 DATA 03 FROM TESTFILE ;

The results look good. In the second result the 5 has lost its leading zero as it is now a number, and not a character value.

Length DATA ------ ---------------------------------- 10 10<-- 10 -->XXXXXXXXXXXXXXXXXXX... 5 05<-5->XXXXXXXXXXXXXXXXXXXXXXXX... 20 20<------- 20 ------->XXXXXXXXX...

Let me try using the TO_NUMBER in the nested substring:

01 SELECT TO_NUMBER(SUBSTR(DATA,1,2)) AS "Length", 02 SUBSTR(DATA,3,TO_NUMBER(SUBSTR(DATA,1,2))) AS "What I want", 03 DATA 04 FROM TESTFILE ;

This errors with the same error as before, SQL state 42815.

Every time I use TO_NUMBER I always receive some comments asking me why I did not use a CAST to convert the character to decimal. Let me try to cast instead:

01 SELECT CAST(SUBSTR(DATA,1,2) AS DEC(2,0)) AS "Length", 02 SUBSTR(DATA,3,CAST(SUBSTR(DATA,1,2) AS DEC(2,0))) AS "What I want", 03 DATA 04 FROM TESTFILE ;

Using the CAST made no difference and the statement errors with the same SQL state as before.

To be able to use a nested substring it must return an integer value. In the example below I have defined the result as integer, INT. I have not bothered to give the integer value a length as Db2 for i will define it with a default length.

My statement looks like:

01 SELECT CAST(SUBSTR(DATA,1,2) AS INT) AS "Length", 02 SUBSTR(DATA,3,CAST(SUBSTR(DATA,1,2) AS INT)) AS "What I want", 03 DATA 04 FROM TESTFILE ;

And when I execute the above statement I get the results I desired:

Length What I want DATA ------ -------------------- ---------------------------------- 10 <-- 10 --> 10<-- 10 -->XXXXXXXXXXXXXXXXXXX... 05 <-5-> 05<-5->XXXXXXXXXXXXXXXXXXXXXXXX... 20 <------- 20 -------> 20<------- 20 ------->XXXXXXXXX...

I have the key values wanted extracted from DATA.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.