When executing a SELECT statement with SUBSTRING() function on
table organized by column, error " SQL0138N The statement was
not executed because a numeric argument of a scalar function is
out of range" is returned
Here's the scenario:
CREATE TABLE ROW_TABLE ( ST CHAR(02),LOW VARCHAR(25) )
DISTRIBUTE BY RANDOM ORGANIZE BY ROW
DB20000I The SQL command completed successfully.
INSERT INTO "ROW_TABLE" (ST, LOW) VALUES ('OK', '36673')
DB20000I The SQL command completed successfully.
CREATE TABLE COLUMN_TABLE ( ST CHAR(02),LOW VARCHAR(25) )
DISTRIBUTE BY RANDOM ORGANIZE BY COLUMN
DB20000I The SQL command completed successfully.
INSERT INTO "COLUMN_TABLE" (ST, LOW) VALUES ('OK', '36673')
DB20000I The SQL command completed successfully.
SELECT T0.C0 AS C0,T0.C1 AS C1,T0.C2 AS C2,T0.C3 AS C3,(CASE
WHEN (T0.C2 > 0) THEN SUBSTRING(T0.C1, 1, T0.C3,CODEUNITS32)
ELSE NULL END) AS C4 FROM (SELECT T0.C0 AS C0,T0.C1 AS C1,(CASE
WHEN (T0.C2 IS NULL) THEN 0 ELSE T0.C2 END) AS C2,((CASE WHEN
(T0.C2 IS NULL) THEN 0 ELSE T0.C2 END) - 1) AS C3 FROM (SELECT
T0.ST AS C0,T0.LOW AS C1,LOCATE('-', T0.LOW, CODEUNITS32) AS C2
FROM COLUMN_TABLE T0) T0) T0
C0 C1 C2 C3 C4
-- ------------------------- ----------- -----------
-------------------------
OK 36673 0 -1 -
SQL0138N The statement was not executed because a numeric
argument of a
scalar function is out of range. SQLSTATE=22011
SELECT T0.C0 AS C0,T0.C1 AS C1,T0.C2 AS C2,T0.C3 AS C3,(CASE
WHEN (T0.C2 > 0) THEN SUBSTRING(T0.C1, 1, T0.C3,CODEUNITS32)
ELSE NULL END) AS C4 FROM (SELECT T0.C0 AS C0,T0.C1 AS C1,(CASE
WHEN (T0.C2 IS NULL) THEN 0 ELSE T0.C2 END) AS C2,((CASE WHEN
(T0.C2 IS NULL) THEN 0 ELSE T0.C2 END) - 1) AS C3 FROM (SELECT
T0.ST AS C0,T0.LOW AS C1,LOCATE('-', T0.LOW, CODEUNITS32) AS C2
FROM ROW_TABLE T0) T0) T0
C0 C1 C2 C3 C4
-- ------------------------- ----------- -----------
-------------------------
OK 36673 0 -1 -
With following environment:
DB2_WORKLOAD=ANALYTICS
DB2_USE_ALTERNATE_PAGE_CLEANING=ON [DB2_WORKLOAD]
DB2_DIRECT_IO=NO
DB2_ANTIJOIN=EXTEND [DB2_WORKLOAD] |
