All Forums Database
Vivek79 13 posts Joined 04/15
23 Feb 2016
Execute Dynamic Select into string in Teradata

This is the MYSQL query, now I need this below dynamic query to be execute in TERADATA SQL.


    set l_sql=concat('SELECT max(',l_rid_col,'), MAX(cid) INTO @c2, @c3 FROM ',p_database,'.',p_table);
    SET l_rid = @c2;
    SET l_cid = @c3;

And this update query:


    update table_a set row =ifnull(l_rid, 0),
                           column= ifnull(l_cid, 0) where databasename=p_database and tablename=p_table;
But in Teradata, I tried this way:


    update table_a as a from (select max(l_rid) TR, MAX(l_cid)  TCC from DEVP.employees )as b


    set a.row= b.TR , a.column=b.TCC where a.databasename='DEVP' and a.tablename='employees';


But i'm getting this error:

STATEMENT 1: Update Statement failed.  Elapsed time = 00:00:00.015 Executed as Single statement.  Failed [3993 : HY000] Illegal usage of alias name.

Are we having any solution for this, in teradata?

Vivek79 13 posts Joined 04/15
23 Feb 2016

I got the answer, here


update table_a from (select max(l_rid) TR, MAX(l_cid) TCC from DEVP.employees )as b set row= b.TR , column=b.TCC where databasename='DEVP' and tablename='employees';


ISSUE: I just removed the alias name in UPDATE. finally got it.

You must sign in to leave a comment.