Teradata query doubt important

Database

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

7 REPLIES
Enthusiast

Re: Teradata query doubt important

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

Enthusiast

Re: Teradata query doubt important

This manual should help you to get started...

www.teradataforum.com/teradata_pdf/b035-1148-115a.pdf

Senior Apprentice

Re: Teradata query doubt important

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

Re: Teradata query doubt important

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

Senior Apprentice

Re: Teradata query doubt important

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

Re: Teradata query doubt important

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

Senior Apprentice

Re: Teradata query doubt important

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