Numeric Overflow in Recursive Query : Teradata

Question

I'm new to teradata. I want to insert numbers 1 to 1000 into the table test_seq, which is created as below.

create table test_seq(
    seq_id integer
);

After searching on this site, I came up with recusrive query to insert the numbers.

insert into test_seq(seq_id)
with recursive cte(id) as (
    select 1 from test_dual
    union all
    select id + 1 from cte
    where id + 1 <= 1000
    )
select id from cte;

test_dual is created as follows and it contains just a single value. (something like DUAL in Oracle)

create table test_dual(
    test_dummy varchar(1)
);

insert into test_dual values ('X');

But, when I run the insert statement, I get the error, Failure 2616 Numeric overflow occurred during computation.

What did I do wrong here? Isn't the integer datatype enough to hold numeric value 1000? Also, is there a way to write the query so that i can do away with test_dual table?

score 7 · Accepted Answer · answered Aug 05 '13 at 09:43

When you simply write 1 the parser assigns the best matching datatype to it, which is a BYTEINT. The valid range of values for BYTEINT is -128 to 127, so just add a typecast to INT :-)

Usually you don't need a dummy DUAL table in Teradata, "SELECT 1;" is valid, but in some cases the parser still insists on a FROM (don't ask me why). This trick should work:

SEL * FROM (SELECT 1 AS x) AS dt;

You can create a view on this:

REPLACE VIEW oDUAL AS SELECT * FROM (SELECT 'X' AS dummy) AS dt;

Explain "SELECT 1 FROM oDUAL;" is a bit stupid, so a real table might be better. But to get efficient access (= single AMP/single row) it must be defined as follows:

CREATE TABLE dual_tbl(
    dummy VARCHAR(1) CHECK ( dummy = 'X') 
) UNIQUE PRIMARY INDEX(dummy); -- i remember having fun when you inserted another row in Oracle's DUAL :_)

INSERT INTO dual_tbl VALUES ('X'); 

REPLACE VIEW oDUAL AS SELECT dummy FROM dual_tbl WHERE dummy = 'X';

insert into test_seq(seq_id)
with recursive cte(id) as (
    select cast(1 as int) from oDUAL
    union all
    select id + 1 from cte
    where id + 1 <= 1000
    )
select id from cte;

But recursion is not an appropriate way to get a range of numbers as it's sequential and always an "all-AMP step" even if it the data resides on a single AMP like in this case.

If it's less than 73414 values (201 years) better use sys_calendar.calendar (or any other table with a known sequence of numbers) :

SELECT day_of_calendar 
FROM sys_calendar.CALENDAR
WHERE day_of_calendar BETWEEN 1 AND 1000;

Otherwise use CROSS joins, e.g. to get numbers from 1 to 1,000,000:

WITH cte (i) AS 
 ( SELECT day_of_calendar
   FROM sys_calendar.CALENDAR
   WHERE day_of_calendar BETWEEN 1 AND 1000
 ) 
SELECT 
  (t2.i - 1) * 1000 + t1.i
FROM cte AS t1 CROSS JOIN cte AS t2;

that BYTEINT thing is real informative , thanks so much. Recursive sql's always insist for a from clause I wonder why teradata as nothing like dual. — dev, Jul 19 '16 at 09:21
@etl_devs: As `select 1;` is valid in Teradata there was no need for a dual table. But when they added set operations like `UNION` they started using Standard SQL compliant rules which requires `FROM` . You could create a dummy, just don't name it `DUAL` as it's a reserved keyword and it must be written as `"DUAL"`: `REPLACE VIEW oDual AS SELECT 'X' AS DUMMY;` — dnoeth, Jul 19 '16 at 09:55

Numeric Overflow in Recursive Query : Teradata

1 Answers1