while creating a UDF it seems an embedded SQL query cannot deal with function parameters, for example in the where-clause of the query.
The following code snipped is an example of the problem:
CREATE OR REPLACE FUNCTION MY_FUNC(myValue INTEGER) RETURN INTEGER IS cnt INTEGER; BEGIN cnt := select count(*) from MY_TABLE where myColumn=myValue; RETURN cnt; END /
When I call the function, e.g.:
I get the following error:
[0A000] Feature not supported: function parameters may not be accessed within scalar subqueries (Session: 1664854486638338049)
Does anyone know how to get around this?
Depending on the context (which I don't know) this might help in your case.
create or replace connection exa_connection
identified by 'exasol';
create or replace python scalar script MY_FUNC(myValue INTEGER)
c = exa.get_connection('EXA_CONNECTION')
with EXASOL.connect(c.address, c.user, c.password) as connection:
with connection.cursor() as cursor:
cursor.execute("select count(*) from test.tmp where id_3 = ?", ctx.myValue)
ret = cursor.fetchone()
Hope that helps
Hi @exa-AndreasS ,
I encountered the same problem while creating UDF. I tried swapping function parameter with created within UDF variable but I got the same error.
Is it possible to somehow access the schema with a SELECT inside the UDF(to use information stored in the tables) and use defined variables in the SELECT statement ? Is it maybe possible to do that with LUA script ?
// LIKE: z := (SELECT x FROM y where x = variable_defined_in_UDF) //
I am asking that 'cos using UDF with python works really slow and I was hoping to use plain procedural SQL to do that and my hope was it would be as fast as builtin functions.
to not mess things up, you maybe should open another discussion with your exact problem description.
Just to mention two things aside,
- procedural SQL is usually not the best approach to code in a columnar database (key thing here: storage organization within database)
- depending on what you want to achieve it might be a better use case for a "script" in Exasol: https://docs.exasol.com/database_concepts/scripting.htm
if this is not suitable solving your issue, please open a new conversation with more details on your specific case.
How your system looks like (DDL, sample data, ...) and what you try to achieve.