Execute Dynamic Select into string in Teradata

Database
Enthusiast

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?
Tags (2)
1 REPLY
Enthusiast

Re: Execute Dynamic Select into string in Teradata

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.