How to use fetch in a dynamic cursor

Database
Junior Contributor

Re: How to use fetch in a dynamic cursor

Strange, works on a 13.00.00.21

Maybe it's a connection or client problem, don't know.

Could you put the source code in a file and try a .COMPILE from BTEQ?

Or try another express version/client/connection...

Dieter

Enthusiast

Re: How to use fetch in a dynamic cursor

Thanks Dieter. Glad to know that it is not fetched sequentially. I understand about the note about putting these in MACROs. The customer has a lot of SQL Server stored procedures and we are working on a functional migration rather than rewriting any of the SPs. Tough choice....but have to stay within budget and time. Your note mentioned that it is simple to rewrite as as single SELECT in a view. Are you proposing something like this?

REPLACE proc b2b_se_sign_s ( IN se10 BIGINT, IN max_age_days INT) 
DYNAMIC RESULT SETS 1
BEGIN
SET max_age_dt = CURRENT_DATE - :max_age_days;
DECLARE cur1 CURSOR WITH RETURN ONLY TO CLIENT FOR
SELECT ss1.se10                ,
       ss1.dba_nm              ,
       ss1.insert_dt           ,
       ss1.insert_dt AS open_dt,
       'Y'           AS recent_sign_ind
FROM   se_sign SS1
WHERE  ss1.seq_nbr = ( SELECT  MAX(ss1.seq_nbr)
FROM     se_sign ss1
WHERE    ss1.se10      = :se10
AND      ss1.insert_dt > max_age_dt
GROUP BY ss1.se10
);
OPEN cur1;
END;
Junior Contributor

Re: How to use fetch in a dynamic cursor

Sorry, of course not a single select in a view, because you need parameters, i meant a macro:

REPLACE MACRO b2b_se_sign_s ( se10 BIGINT, max_age_days INT) AS 
(
SELECT ss1.se10 ,
ss1.dba_nm ,
ss1.insert_dt ,
ss1.insert_dt AS open_dt,
'Y' AS recent_sign_ind
FROM se_sign SS1
WHERE ss1.seq_nbr = ( SELECT MAX(ss1.seq_nbr)
FROM se_sign ss1
WHERE ss1.se10 = :se10
AND ss1.insert_dt > CURRENT_DATE - COALESCE(:max_age_days,21)
GROUP BY ss1.se10);
)

Dieter

Highlighted
Enthusiast

Re: How to use fetch in a dynamic cursor

Thanks, Dieter,

it worked for

LANGUAGE SUPPORT MODE    Standard

RELEASE    13.00.01.13

VERSION    13.00.01.13

By the way, is it possible to declare cursor not only for Select statement, but also for "Help 'sql'" or call procedure, which returns a cursor?

Thanks!

Junior Contributor

Re: How to use fetch in a dynamic cursor

HELP/SHOW/EXPLAIN is not supported in SPs, i don't know why.

And declaring a cursor on the result set of an SP is only supprtted in TD13.10 using ALLOCATE.

Dieter

Not applicable

Re: How to use fetch in a dynamic cursor

Hi All,

Could you please help me as how to loop and fetch in Teradata Dynamic Cursor?

Below is snippet of stored procedure I am using:

SET MY_SQL = 'SEL COLUMNNAME as update_column FROM DBC.COLUMNS where TABLENAME  = '''||TGT_TABLE_NAME||'''' ||' AND DATABASENAME = '''||SRC_DB||'''';

set  t = MY_SQL;

PREPARE MY_STATEMENT FROM MY_SQL;

OPEN UPDATE_CURSOR;

FETCH UPDATE_CURSOR INTO UPD_COLUMNS;

WHILE  SQLSTATE <> '00000'

DO

SET A=A+1;

END WHILE;

this is not working.. Kindly help