Creating UDF/ Function | Assigning Record from DB table to a Variable

Contributor

Hi,

I am working on UDF in which I use Procedural SQL. I want that UDF to be used by users in queries to simplify existing queries.

The purpose of the UDF is to calculate number of business* hours that occurred between two dates in a given country.

 

Arguments of the UDF are:

start_date::timestamp, end_date::timestamp, country_code::CHAR(2) - two letter country code

*hours during day that is not a weekend/ bank holiday in a relevant country

 

While doing above I wanted to use a table(country_holidays AS CH) that we have in our DB - table stores information if a given day in a given country is a business day. If I could get that info from the table and assign to a variable in UDF I would then iterate on the given time range an sum all hours that fall on a business days.

So I wanted to create a variable and assign a value from CH table, like below:

x := SELECT CASE WHEN IS_HOLIDAY(boolean) THEN 0 ELSE 1 END

                    FROM CH

      WHERE CH.variable = UDF.country_code AND CH.date = UDF.date;

This gave me error I cannot use UDF arguments in subquery, then I assigned UDF arguments to another variable and I got same error but about variables in subquery.

[0A000] Feature not supported: function parameters may not be accessed within scalar subqueries (Session: 1664854486638338049)

 

We have a UDF using pandas functions that does above but it is slow, so I wonder if my approach with procedural SQL is ok? Or maybe I can do something like described above but maybe use Lua or something else to do so?

 

Thanks,

Piotr

1 ACCEPTED SOLUTION

Team Exasol
Team Exasol

Hi Piotr,

you wrote: "I am working on UDF in which I use Procedural SQL." And this is the heart of the matter. Instead you want to say: "I am working on Procedural SQL in which I use an UDF.". In Exasol it works the other way around :).
Please have a look at this article which explains the Exasol scripting language landscape in detail: Scripting in Exasol 

 

In Exasol we differentiate between scripts (or scripting scripts if you like) which represent stored procedures and UDF-Scripts (UDFs). Scripts are written in LUA and can run standalone via EXECUTE SCRIPT. UDFs are written in Python, R, Java, LUA or any other language of your choice and can only be used in SQL statements.

This is where the solution to your problem is. You want to use a LUA script to access the contents of your table (via our query() - LUA function). After you fetched the result set in a local LUA variable you can then use the contents of that result set in a SQL statement (in the same LUA script) which utilizes your UDF.
If your logic only relies on the contents of your tables it might be even easier to only write the whole logic in the LUA script and don't use an UDF at all 🙂

 

Hope this helps
Lennart from Exasol

View solution in original post

3 REPLIES 3

Team Exasol
Team Exasol

Hi Piotr,

you wrote: "I am working on UDF in which I use Procedural SQL." And this is the heart of the matter. Instead you want to say: "I am working on Procedural SQL in which I use an UDF.". In Exasol it works the other way around :).
Please have a look at this article which explains the Exasol scripting language landscape in detail: Scripting in Exasol 

 

In Exasol we differentiate between scripts (or scripting scripts if you like) which represent stored procedures and UDF-Scripts (UDFs). Scripts are written in LUA and can run standalone via EXECUTE SCRIPT. UDFs are written in Python, R, Java, LUA or any other language of your choice and can only be used in SQL statements.

This is where the solution to your problem is. You want to use a LUA script to access the contents of your table (via our query() - LUA function). After you fetched the result set in a local LUA variable you can then use the contents of that result set in a SQL statement (in the same LUA script) which utilizes your UDF.
If your logic only relies on the contents of your tables it might be even easier to only write the whole logic in the LUA script and don't use an UDF at all 🙂

 

Hope this helps
Lennart from Exasol

View solution in original post

Contributor

Hi all,

Thanks! I might not been 100% precise but you pointed me in the right direction. 

Thanks again,

Piotr

Team Exasol
Team Exasol

Hi Piotr,
am I right that you currently try to create a function like this and then get the error message:
https://docs.exasol.com/sql/create_function.htm
Seems like you ran into the limitation of the last point in the "usage notes".
I would recommend you to implement this as a UDF script in Lua:
https://docs.exasol.com/database_concepts/udf_scripts.htm
From the scripting languages, Lua is in general the most performant in Exasol.
I assume you might need a scalar/returns in the signature. For your convenience, use this:
https://docs.exasol.com/database_concepts/udf_script_signature_generator.html

Note there is a difference to Lua scripting in Exasol:
https://docs.exasol.com/database_concepts/scripting.htm

Nevertheless, I didn't understand 100% what you are trying to achieve. If you still struggle, more explanation would be good.
Hope that helped!

Franz