EXEC command not working as expected in Datasphere SQL view

EshwarReddy — Mon, 05 Aug 2024 07:06:29 GMT

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

Re: EXEC command not working as expected in Datasphere SQ...

Simon_Ye — Wed, 07 Aug 2024 03:22:31 GMT

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;

Question Re: EXEC command not working as expected in Datasphere SQ... in Technology Q&A

EXEC command not working as expected in Datasphere SQL view

Re: EXEC command not working as expected in Datasphere SQ...