Hello I need to convert to Teradata this sql query :
DECLARE @contatore AS INTEGER, @MySQL AS VarChar(8000)
SET @contatore = 0
SET @MySQL = 'select 0'
WHILE @contatore < 400 BEGIN
SET @contatore = @contatore + 1
set @MySQL = @MySQL + ' union select ' + CAST(@contatore AS VARCHAR)
I could use query:
select * from (select 0 as x) dt union select * from (select 1 as x) dt...........................select * from (select 400 as x) dt
but I' d prefer to use a loop
Can you help me pleaseeeeeeeeeee...it' s very important
using a loop to create a range of numbers is not the best way :-)
I prefer a table with numbers in it, if you don't have it you could easily use the sys_calendar:
select day_of_calendar -1 as x from sys_calendar.calendar
where x between 0 and 400
thank you very much...it seems to run..just a question...I have also tried to use a loop creating the procedure :
replace procedure GetQuery
OUT xToken varchar(2000))
declare sqlQuery varchar(2000);
declare sqlQueryNew varchar(2000);
declare n integer;
set n = 0;
set sqlQuery = 'select * from (select 0 as x) dt';
while n <400
set n = n + 1;
set sqlQuery = sqlQuery || ' union ' || 'select * from (select' || n || ' as x) dt';
select :sqlQuery into :xToken ;
This procedure compose the query ''select * from (select 0 as x) dt union select * from (select 1 as x) dt...........................select * from (select 400 as x) dt"
but how can I execute it?
I 've tried with EXECUTE IMMEDIATE (:sqlQuery) and CALL DBC.SYSEXECSQL(:sqlQuery)
but it doesn't work
You should mention the exact error message instead of "doesn't work".
When you want a SP to return answer sets you have to define it with "dynamic result sets":
replace procedure GetQuery(OUT xToken varchar(2000)) dynamic resuklt sets 1
and use a cursor for the select instead of "execute immediate":
declare c cursor with return only for s;
prepare s1 from sqlQuery;
But, you defined sqlQuery as a VarChar(2000) and try to insert 400 times "select * from (select ... as x) dt union", which estimates to roughly 16KB.
And you forgot to add a final semicolon to the query text.
I think it would be better not use a loop, it takes long time to execute.
Do you think using sys_calendar is the only way?
I can' t use a table with numbers in it
You should try to avoid loops and cursor logic (which is serial) in a parallel database system :-)
You can use any table with numbers in it, you could even use a ROW_NUMBER on any table large enough.
Sys_calendar is just handy, as it exists on every system, it's only limited to 73414 rows.
This is how i create my number table, you could use the same logic within a SP without the INSERT:
/*** Helper table with integer values from 0 to x (9999999) ***/
CREATE VOLATILE TABLE vt_nums ( n INT)
ON COMMIT PRESERVE ROWS;
INSERT INTO vt_nums VALUES(0)
;INSERT INTO vt_nums VALUES(1)
;INSERT INTO vt_nums VALUES(2)
;INSERT INTO vt_nums VALUES(3)
;INSERT INTO vt_nums VALUES(4)
;INSERT INTO vt_nums VALUES(5)
;INSERT INTO vt_nums VALUES(6)
;INSERT INTO vt_nums VALUES(7)
;INSERT INTO vt_nums VALUES(8)
;INSERT INTO vt_nums VALUES(9)
CREATE TABLE numbers(n INT NOT NULL PRIMARY KEY);
INSERT INTO numbers
i2.n * 10 +
i3.n * 100 +
i4.n * 1000 +
i5.n * 10000 +
i6.n * 100000
vt_nums i1, vt_nums i2, vt_nums i3, vt_nums i4, vt_nums i5, vt_nums i6
DROP TABLE vt_nums;
COLLECT STATISTICS ON numbers COLUMN n;