Copy Substring from CLOB field with SQL

Asked By 20 points N/A Posted on -
qa-featured

How would you copy substring from CLOB field with SQL if substring from/to location are variables?

This exercise is OK (RPG free with embedded SQL):

exec sql choice substr(myclobfield, 25, 70)

                INTO :mystring

                FROM MYLIB/MYFILE;

[/pre]

Since this source is a failure of compilation

from = 25;

to = 70;

exec sql select substr(myclobfield, :from, :to)

                INTO :mystring

                FROM MYLIB/MYFILE;

[/pre]

The following is a compilation failure:

MSG ID SEV RECORD TEXT

SQL0171 30 27 Position 1 Plot 2 of SUBSTRING function is not valid.

SQL0171 30 27 Position 1 Plot 3 of SUBSTRING function is not valid.

[/ Pre]

Can someone please explain why this is not working, and please give some information on how to circumvent or bypass, whereas the location of the content of CLOB field is changing from record to record, the application of the need to/from the location should be variable. Please help. Thanks.

SHARE
Answered By 10 points N/A #91146

Copy Substring from CLOB field with SQL

qa-featured

The problem may be in how you have declared the variables. When using SQL, any little of variables for instance at a place where you are supposed to use characters and you end up using integers may cause such kind of a problem. For that matter, you will need to try restructuring the variables to 10I,0, for in your case it appears that the function that you are using requires an integer type, while in the code that you have pasted above you are using the string which allows characters to be used in it. Or just go to the code and change that and see if the program will work.

-Thompson Locker

 

Related Questions