sqlluaexasolution

Executing query in query function using a variable in Exasol

I am trying to execute dynamic query in Exasol using script function. Following is the code:

CREATE or replace SCRIPT script_2(a)
RETURNS TABLE AS
quer="select * from table_name cross join table_name"
for i=1, 3 do
    quer=quer .. " table_name"
    print(query)
end
exit(query([[quer]]))
/

Not able to get the output of this script. I get this error while calling execute for the script. Below is the error:

43000:"syntax error, unexpected end_of_input, expecting assignment_operator or ':' [line 1, column 1]" caught in script "xxxxxxxxxx"."SCRIPT_2" at line 6

Solution

  • Remove double square brackets around varible name.

    create or replace script script_2 (n)
returns table as
local quer = "select * from (values(0))t0(t0)";
for i = 1, n do
    quer = quer.." cross join (values("..i.."))t"..i.."(t"..i..")";
    print (quer);
end
exit(query(quer));
/