CODEWITHSUNDEEP
teradata
Emwi
Emwi

Reputation: 21

Teradata Insert Count into Variable

Description what I am trying to do:

I have 2 environments one has data (X) second one has no data (Y).

I have done procedure which has input parameter P_TableName. It should check if in this table is any data and IF There is then we will take data to Y environment.

So Mostly it works but I have problem with one freaking simple thing ( I have not much experience in TD but in Oracle it would be a 10seconds).

I need to pass select count(*) from X to variable how to do that?.

I was trying by SET VAR = SELECT... INSERT INTO VAR SELECT... I was trying to make a variable for statement which is directly executing 

        SET v_sql_stmt = 'INSERT INTO ' || VAR|| ' SELECT COUNT(*) FROM ' || P_TableName;
        CALL DBC.SYSEXECSQL(v_sql_stmt);

It's probably really simple thing but I can't find good solution for that. Please help

Upvotes: 0

Views: 1714

Answers (2)

dnoeth
dnoeth

Reputation: 60462

You can't use INTO in Dynamic SQL in Teradata.

As a workaround you need to do a cursor returning a single row:

DECLARE cnt BIGINT;
DECLARE cnt_cursor CURSOR FOR S;
SET v_sql_stmt = ' SELECT COUNT(*) FROM ' || P_TableName;
PREPARE S FROM v_sql_stmt;
OPEN cnt_cursor;
FETCH cnt_cursor INTO cnt; 
CLOSE cnt_cursor;

Upvotes: 1

JNevill
JNevill

Reputation: 50034

You'll have to open a cursor to fetch the results since you are running dynamic SQL. There is a good example in the Teradata help doc on Dynamic SQL:

CREATE PROCEDURE GetEmployeeSalary
(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))
BEGIN
  DECLARE SqlStr VARCHAR(1000);
  DECLARE C1 CURSOR FOR S1;
  SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';
  PREPARE S1 FROM SqlStr;
  OPEN C1 USING EmpName;
  FETCH C1 INTO Salary;
  CLOSE C1;
END;

Upvotes: 1

Related Questions