Function parameters may not be accessed within scalar subqueries

Community Manager
Community Manager

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

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...
3 REPLIES 3

Team Exasol
Team Exasol

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

Contributor

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

 

 

Team Exasol
Team Exasol

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