How to use variables in SQL queries on DataGrip (Exasol dialect)?

Question

Database: EXASOL

IDE: IntelliJ DataGrip

I am trying to declare variables in SQL and call them throughout the query. This is the equivalent in Microsoft SQL Server:

DECLARE @var AS INT = 3
SELECT @var AS var
-- Use case example:
SELECT * FROM table1 WHERE column_value = @var

Is this possible in DataGrip and Exasol? I searched the documentation (General Script Language, Database Interaction) of Exasol where they describe the following:

a = 3
SELECT :a

However, this opens a console where I am asked to type the value of a. I don't want to type the values of variables every time I execute the code. I want to set a variable and use it on different parts of the query, just like any other high-level programming language.

I found two similar questions on JetBrains' forum (1, 2) but they are unanswered. Another one found on StackOverflow (url) just stated that the dialect is not supported on DataGrips.

Does anyone know how to solve this? Is it simply not supported? It would really increase productivity for me and my team.

Thank you in advance!

As far as I get, it is the limitation of the database itself: https://stackoverflow.com/questions/53959257/variable-declaration-in-exasol — moscas, Jul 20 '21 at 19:27

score 2 · Accepted Answer · answered Aug 12 '21 at 09:18

After spending some time, I found out that this is not possible. Instead Exasol allows LUA scripts that can run such calculations. Below you will find an example:

CREATE LUA SCRIPT "TEST" (p_country) RETURNS TABLE AS
local param_c = p_country
exit(
    query(
        [[
            SELECT * FROM SCHEMA_NAME.TABLE_NAME
            WHERE SK_COUNTRY = :local_c;
        ]]
        ,{local_c=param_c}
        )
    );
/

EXECUTE SCRIPT SCHEMA_NAME.TEST('DE');

In this example the keyword RETURNS TABLE outputs the table results from this query. The keyword exit() is similar to a print() method. And lastly, I don't know why but the function parameter needs to be assigned to a local variable, which then needs to be assigned to another variable in the query. This makes no sense to me, but I could not get it to work otherwise.

In my example I have the script parameter p_country which is assigned to the local parameter param_c which is then assigned to the query parameter local_c.

You can find the documentation under:

How to use variables in SQL queries on DataGrip (Exasol dialect)?

1 Answers1