Execute select statement with list in variable gives 'Unsupported feature 'assignment from-CodePudding

I'm trying to select table columns that are same in myTable1 and myTable2 and than do a select statement upon those columns in myTable1.

So this works, getting same columns into list in variable:

set columnNames = (select listagg(a.*,',') from
(SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable1') a
inner join
(SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS b
WHERE TABLE_NAME = 'myTable2') b
on a.column_name = b.column_name);

but this fails:

SET sqlText = 'SELECT '   $columnNames   ' from myDB.MySchema.myTable
where sold > 0 and hour = ''2022-08-01 13:00:00.000'' 
and id = ''zzzseee2323''
limit 10';

with error:

000002 (0A000): Unsupported feature 'assignment from non-constant source expression'.

So how to concatenate that variable with text and execute statement? I would on the end execute just with

EXECUTE IMMEDIATE $sqlText

CodePudding user response：

The size of session variableSET is 256bytes and most likely will not be usable in this context.

This case could be resolved using Snowflake Scripting:

DECLARE   
    res RESULTSET;
    query TEXT;
BEGIN 
   LET columnNames := (SELECT listagg(a.*,',') FROM
    (SELECT column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'myTable1') a
    inner join
    (SELECT column_name
    FROM INFORMATION_SCHEMA.COLUMNS b
    WHERE TABLE_NAME = 'myTable2') b
    on a.column_name = b.column_name);

    query := 'SELECT '   :columnNames   ' 
               from myDB.MySchema.myTable
               where sold > 0 and hour = ''2022-08-01 13:00:00.000'' 
                 and id = ''zzzseee2323''
               limit 10';

    res := (EXECUTE IMMEDIATE :query);
    RETURN TABLE (res);

END;