Query using SUBSTRING on a column of type CLOB

sasanka · New User Joined: 18 Jan 2008 Posts: 34 Location: India

Hi,

I am using a table in which one column is defined as below:

EMP_INFO CLOB (5M) NOT NULL

I am storing data of size 342575 bytes into column EMP_INFO

After inserting rows into this table, I am trying to use SUBSTRING to see the last 100 bytes of stored data and using below query (in SPUFI):

SELECT SUBSTRING(CLOB(EMP_INFO), 342475, 100)
FROM EMP_TAB
FETCH FIRST 1 ROW ONLY
WITH UR;

But, I am getting SQLCODE # -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 3 OF SUBSTRING IS INVALID

Please someone help me to correct my query.

Thanks.

Nic Clouston · Posted: Mon Jul 04, 2016 3:51 pm

Have you run a query to determine the actual length of each value of EMP_INFO?

Why did you not use the code tags for your query? Code tags should be used for all data and code.

sasanka · New User Joined: 18 Jan 2008 Posts: 34 Location: India

Hi,

The actual length of EMP_INFO is 342575 and I am executing the query in SPUFI and got the sql error as mention in my earlier post.

Thanks..

sasanka · New User Joined: 18 Jan 2008 Posts: 34 Location: India

Hi,

Can anyone please provide me a correct query with SUBSTRING used on a CLOB column of a table.

Thanks

Rohit Umarjikar · Posted: Tue Jul 05, 2016 9:31 pm

Let us see if you are right about the offset, Try this.

Nic Clouston · Posted: Tue Jul 05, 2016 9:40 pm