Dynamic Select Clause

Database

Dynamic Select Clause

Hello,

 

I'm Running Stored Procedure with loop.

I want to calculate average for different columns dynamically. 

I Find the number of columns in the table and the column names.

When i'm running the query with the specific column i want to calculate i get bad character error.

Can i change the select columns dynamically ?

 

Thanks

REPLACE PROCEDURE DB_Name.SP_Name ()BEGIN
    
    DECLARE i INTEGER;
    DECLARE n_columns INTEGER;
    DECLARE column_calc VARCHAR(50);
    
    SET i=1;
    
    SELECT  COUNT(*) INTO  n_columns FROM DBC.COLUMNS WHERE DatabaseName='DB_Name' AND TABLENAME='TABLENAME_1';
    
    WHILE (i<= n_columns) DO
    BEGIN
            
            SELECT ColumnName INTO column_calc 
            FROM DBC.COLUMNS 
            WHERE DatabaseName='DB_Name' 
            AND TABLENAME='TABLENAME_1'
            QUALIFY ROW_NUMBER() OVER(ORDER BY 1) = i;
            
            INSERT INTO  DB_Name.TABLENAME_2    
            SELECT AVG(column_calc) FROM  DB_Name.TABLENAME_1;
                    
            SET i=i+1;
            
    END;
    END WHILE;
END;

 

2 REPLIES
Teradata Employee

Re: Dynamic Select Clause

The Insert-select avg() statement has to be dynamically constructed and then explicitly executed.  See the Execute (Immediate) section in the Stored Procedures manual for instructions.  For example, you might do:

Execute Immediate 'INSERT INTO DB_Name.TABLENAME_2 SELECT AVG(' || :column_calc || ') FROM DB_Name.TABLENAME_1';

Re: Dynamic Select Clause

Thanks, it works