RPAD and LPAD gives: SELECT Failed [9804] Response Row size or Constant Row size

Database
Enthusiast

RPAD and LPAD gives: SELECT Failed [9804] Response Row size or Constant Row size

Is it not possible to use RPAD and LPAD more than 2 times in the same QUERY? It cannot be right.

This small example gives error when running it:

DROP TABLE t1;
CREATE MULTISET TABLE t1 (h INT, m INT, s INT);
DELETE FROM t1;
INSERT INTO t1 VALUES (1,2,3);

-- Result in Error: SELECT Failed [9804] Response Row size or Constant Row size overflow.
SELECT LPAD(CAST(h AS VARCHAR(2)),2,'0') AS h,
LPAD(CAST(m AS VARCHAR(2)),2,'0') AS m,
LPAD(CAST(s AS VARCHAR(2)),2,'0') AS s
FROM t1;

-- Result in Error: SELECT Failed [9804] Response Row size or Constant Row size overflow.
SELECT RPAD(CAST(h AS VARCHAR(2)),2,'0') AS h,
RPAD(CAST(m AS VARCHAR(2)),2,'0') AS m,
RPAD(CAST(s AS VARCHAR(2)),2,'0') AS s
FROM t1;

-- This Runs
SELECT LPAD(CAST(h AS VARCHAR(2)),2,'0') AS h,
LPAD(CAST(m AS VARCHAR(2)),2,'0') AS m--,
--LPAD(CAST(s AS VARCHAR(2)),2,'0') AS s
FROM t1;

I am using Teradata SQL Assistant Version 13.11.0.05 (English)

On Teradata version 14.10

Logging on with Teradata.net

Peter Schwennesen

Tags (3)
3 REPLIES
Enthusiast

Re: RPAD and LPAD gives: SELECT Failed [9804] Response Row size or Constant Row size

Its looks like there is a similar problem with STRTOK

DROP TABLE T2;
CREATE MULTISET VOLATILE TABLE t2 (Str VARCHAR(30)) ON COMMIT PRESERVE ROWS;
DELETE FROM T2; INSERT INTO T2 VALUES ('1,2,3,4,5,6,7,8,9,10,11');

This runs:

SELECT STRTOK(Str,',', 1),
STRTOK(Str,',', 2),
STRTOK(Str,',', 3),
--STRTOK(Str,',', 4),
--STRTOK(Str,',', 5),
--STRTOK(Str,',', 6),
STRTOK(Str,',', 7),
STRTOK(Str,',', 8),
STRTOK(Str,',', 9),
STRTOK(Str,',',10),
STRTOK(Str,',',11)
FROM T2;

But this does not:

SELECT STRTOK(Str,',', 1),
STRTOK(Str,',', 2),
STRTOK(Str,',', 3),
--STRTOK(Str,',', 4),
STRTOK(Str,',', 5),
--STRTOK(Str,',', 6),
STRTOK(Str,',', 7),
STRTOK(Str,',', 8),
STRTOK(Str,',', 9),
STRTOK(Str,',',10),
STRTOK(Str,',',11)
FROM T2;

Peter Schwennesen

Senior Apprentice

Re: RPAD and LPAD gives: SELECT Failed [9804] Response Row size or Constant Row size

Hi Peter,

LPAD returns a VARCHAR(32000) CHARACTER SET UNICODE, you need to cast it to an matching size:

SELECT LPAD(CAST(h AS VARCHAR(2)),2,'0') AS h, 
LPAD(CAST(m AS VARCHAR(2)),2,'0') AS m,
LPAD(CAST(s AS VARCHAR(2)),2,'0') AS s
FROM t1;

Regarding STRTOK, this works on my TD15, check the size using      

SELECT TYPE(STRTOK(Str,',',11)) FROM T2;
Enthusiast

Re: RPAD and LPAD gives: SELECT Failed [9804] Response Row size or Constant Row size

Hi Dieter

Thanks, I do not understand the internals of the LPAD and RPAD. Its defined that the max output length is 2, even then it returns a VARCHAR(32000) strange.

Hovere this works now:

DROP TABLE t1;
CREATE MULTISET TABLE t1 (h VARCHAR(2), m VARCHAR(2), s VARCHAR(2));
DELETE FROM t1;
INSERT INTO t1 VALUES ('1','2','3');

SELECT CAST(LPAD(h, 2, '0') AS VARCHAR(2)) AS h,
CAST(RPAD(m, 2, '0') AS VARCHAR(2)) AS m,
CAST(LPAD(s, 2, '0') AS VARCHAR(2)) AS s
FROM t1;

I just tried the casting on the STRTOK, and I think it is some kind of similar problem here. Casting makes it work too.

This is now working now:

DROP TABLE T2;
CREATE MULTISET VOLATILE TABLE t2 (Str VARCHAR(30)) ON COMMIT PRESERVE ROWS;
DELETE FROM T2; INSERT INTO T2 VALUES ('1,2,3,4,5,6,7,8,9,10,11');

SELECT CAST(STRTOK(Str,',', 1) AS INTEGER),
CAST(STRTOK(Str,',', 2) AS INTEGER),
CAST(STRTOK(Str,',', 3) AS INTEGER),
CAST(STRTOK(Str,',', 4) AS INTEGER),
CAST(STRTOK(Str,',', 5) AS INTEGER),
CAST(STRTOK(Str,',', 6) AS INTEGER),
CAST(STRTOK(Str,',', 7) AS INTEGER),
CAST(STRTOK(Str,',', 8) AS INTEGER),
CAST(STRTOK(Str,',', 9) AS INTEGER),
CAST(STRTOK(Str,',',10) AS INTEGER),
CAST(STRTOK(Str,',',11) AS INTEGER)
FROM T2;

Peter Schwennesen