Variable results from an sql statement

Database

Variable results from an sql statement

Is there a way to assign a value to a variable inside a procedure with the results from an sql statement?

EXAMPLE:

DECLARE t_dups INTEGER DEFAULT 0;

SET sql_stmt =

' SELECT COUNT(*) INTO ' || t_dups ||

' FROM DM_JNJCHF_TEST' ||

' WHERE PT_ID = ' || t_random_num ;

EXECUTE IMMEDIATE sql_stmt;

I've tried multiple ways but can't get the right syntax.  The SET statement resides within a CURSOR LOOP and is needed to cycle back thru.

 

Thanks, Bill

6 REPLIES

Re: Variable results from an sql statement

Hey Teradata Forum,

I found a work around for the above issue using the 'GET DIAGNOSTICS' function.  My problem was making sure pt_id was truly unique using a random number so I needed to cycle thru until one was found.  If the row_count returned 1(meaning 1 row was updated) I knew it was a unique number.  This is done by checking the value of t_dups.

It works for deletes, updates, inserts but not on selects.

DECLARE t_dups INTEGER DEFAULT 0;

SET sql_stmt =

' UPDATE DM_JNJCHF_TEST'||

' SET PT_ID = ' || t_random_num ||

' WHERE PAT_ID = ' || t_pat_id ||

' AND (SELECT COUNT(*) FROM DM_JNJCHF_TEST' ||

' WHERE PT_ID =' || t_random_num ||

' ) = 0';

EXECUTE IMMEDIATE sql_stmt;

GET DIAGNOSTICS t_dups = ROW_COUNT;

Teradata Employee

Re: Variable results from an sql statement

Interesting workaround.

The more general answer is to use a cursor (even if the SELECT returns only one row):

SET sql_stmt = 'SELECT COUNT(*) FROM DM_JNJCHF_TEST WHERE PT_ID = ?'  ; 

DECLARE C1 CURSOR FOR S1;

PREPARE S1 FROM sql_stmt;

OPEN C1 USING t_random_num;

FETCH C1 INTO t_dups;

CLOSE C1;

Re: Variable results from an sql statement

thanks Fred

N/A

Re: Variable results from an sql statement

Hello all,

This is my first post on the TeraData developer forum. So I apologize in advance if my question has already been asked earlier or I did not follow any convention. Also i am new to teradata.

Here I 'm working on requirement to remove unreadable or junk characters from Firstname column. So I have to run the update statement in loop till the count of the select statement becomes zero.

I have the following piece of code which is not working and I am looking for some help to make it execute.

REPLACE PROCEDURE firstname_Test()

BEGIN

DECLARE FINAL_COUNT INTEGER;

SET FINAL_COUNT = '';

FOR CNT1_CURSOR AS CURSOR C_CNT1 FOR

sel count(*) as firstcount from db1.Customer where TRANSLATE_CHK(firstname USING latin_to_unicode) <> 0;

FINAL_COUNT =CNT1_CURSOR.firstcount;

WHILE (FINAL_COUNT<='1')

DO

update db1.Customer

set firstname = substr(firstname,0,TRANSLATE_CHK(firstname USING latin_to_unicode)) || substr(firstname,TRANSLATE_CHK(firstname USING latin_to_unicode)+1)

where TRANSLATE_CHK(firstname USING latin_to_unicode) <> 0;

FOR CNT2_CURSOR AS CURSOR C_CNT2 FOR

sel count(*) as firstcount from db1.Customer where TRANSLATE_CHK(firstname USING latin_to_unicode) <> 0;

FINAL_COUNT =CNT2_CURSOR.firstcount;

END WHILE;

END;

Thanks/Raj

N/A

Re: Variable results from an sql statement

Hi,

I am new to Teradata Stored Procedure. The below is my sample SP. In this am not able to get the desired output(I need to insert table name,column name and count in it to a new table for each column in the input table(to the SP)) but the below code is throwing a run time error (Call Failed 7689: Invalid Dynamic SQL Statement).

Kindly help me in this. This is a little bit urgent requirment.

Code:

REPLACE PROCEDURE t3_rita.CURSOR_SAMPLE (IN v_TABLE_NAME  VARCHAR(100))
BEGIN
DECLARE v_COUNT INTEGER ;
DECLARE my_sql VARCHAR(1000);
DECLARE my_sql_1 VARCHAR(1000);
DECLARE v_COLUMN_NM VARCHAR(100);
DECLARE PROFILE_SAMPLE CURSOR FOR select columnname from dbc.columns
where tablename='BKEY_ERDM_ACCOUNT' and databasename='T3_RITA' order by 1;
OPEN PROFILE_SAMPLE;
L1:
LOOP
FETCH PROFILE_SAMPLE into v_COLUMN_NM;
IF (SQLSTATE = '02000') THEN
LEAVE L1;
END IF;
set my_sql ='select count(*) into v_COUNT from t3_rita.'||v_TABLE_NAME||';';
EXECUTE IMMEDIATE my_sql;
insert into t3_rita. testing_r (v_TABLE_NAME ,v_COLUMN_NM,v_COUNT);
END LOOP L1;
CLOSE PROFILE_SAMPLE;
END;

Thanks and Regards,

Raja M

Re: Variable results from an sql statement