Function parameters may not be accessed within scalar subqueries

[exa-Chris]

Hi,

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.:

select my_func(42);

 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?

Best regards

[exa-AndreasS]

Depending on the context (which I don't know) this might help in your case.

create or replace connection exa_connection
    to 'ws://<ip>:8563'
    user 'SYS'
    identified by 'exasol';

create or replace python scalar script MY_FUNC(myValue INTEGER)

RETURNS INTEGER

AS

import EXASOL

def run(ctx):

    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()

            return ret[0]

/

;        

select MY_FUNC(2);

Hope that helps

[drabu6]

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.

Thanks,

Piotr

[exa-AndreasS]

Hi Piotr,

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

But again,
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.

Best,
Andy