All Forums Database
14 Sep 2012
Teradata query doubt important

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)
END
Exec (@MySQL)
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
 
Mark

Qaisar Kiani 337 posts Joined 11/05
14 Sep 2012

This seems to be like MYSQL Stored Procedure. Why don't you try Teradata stored procedures!

Qaisar Kiani 337 posts Joined 11/05
14 Sep 2012

This manual should help you to get started...
www.teradataforum.com/teradata_pdf/b035-1148-115a.pdf

dnoeth 4628 posts Joined 11/04
15 Sep 2012

Hi Mark,
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
Dieter

Dieter

17 Sep 2012

Hi Dieter,
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))
begin
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
do
set n = n + 1;
set sqlQuery = sqlQuery || ' union ' || 'select * from (select' || n || ' as x) dt';
end while;
select :sqlQuery into :xToken ;
end;
 
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

dnoeth 4628 posts Joined 11/04
17 Sep 2012

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":
begin
  declare c cursor with return only for s;
  prepare s1 from sqlQuery;
  open c1;
end;
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.
Dieter

Dieter

17 Sep 2012

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

dnoeth 4628 posts Joined 11/04
17 Sep 2012

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
  SELECT 
    i1.n +
    i2.n * 10 +
    i3.n * 100 +
    i4.n * 1000 +
    i5.n * 10000 +
    i6.n * 100000 
  FROM
    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;

Dieter

Dieter

You must sign in to leave a comment.