EXEC command not working as expected in Datasphere SQL view

[EshwarReddy]

Hi,

I'm trying to create a Datasphere SQL view type SQLScript and using the EXEC command as below,

DECLARE cur INTEGER:=1;
DECLARE curr TABLE("VAL" BIGINT);
EXEC 'SELECT COUNT(*) INTO ' || cur ||' FROM AUFK';
INSERT INTO :curr values(cur);
RETURN SELECT * FROM :CURR;

Below is the error : 

sql syntax error: incorrect syntax near "1": line 1 col 22 (at pos 22)

But, when I use the plain select as below, it works.

SELECT COUNT(*) INTO curr FROM AUFK;

We need the exec command as we want to run this code in loop for multiple tables. and make AUFK also as a variable for tablename.

Any help is much appreciated.

__PRESENT

[Simon_Ye]

Hi,

First, the dynamic sql you prepared has some issues, it will concentrate the value(1) of variable "cur" into the sql statement.

Second, the dynamic sql does not allow an INTO clause.

Here is my new script, hope it can fulfill your requirements:

DECLARE dyn_sql NVARCHAR(5000);

DECLARE cur ITABLE("VAL" BIGINT);
DECLARE curr TABLE("VAL" BIGINT);

dyn_sql =  'SELECT COUNT(*) as VAL  FROM AUFK';
EXEC dys INTO cur;
INSERT INTO :curr (VAL) SELECT VAL FROM :cur;
RETURN SELECT * FROM :CURR;