Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Using custom variables looking parameter table

wooper
Contributor
set @flt_vondat = (select pParValue FROM tbl_paramter where pParName = "fw_vondat");
set @flt_bisdat = (select pParValue FROM tbl_paramter where pParName = "fw_bisdat");

select ID, events, Logtime from tbl_data where Logtime between @flt_vondat and @flt_bisdat;

Pleas help me with this simple question. How can I transfer this mysql code to exasol
Thanks' in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

PeterK
Xpert

Hi @wooper,

If you want to use the parameters multiple times in the same query you could import them in a CTE (WITH clause) to avoid multiple connections to MySQL:

WITH params AS ( SELECT * FROM (
  IMPORT FROM JDBC AT mysql_connection STATEMENT '
        SELECT pParName AS name, pParValue AS val
        FROM tbl_parameter
        WHERE pParName IN ("fw_vondat","fw_bisdat")
'))
SELECT ID, events, Logtime 
FROM tbl_data 
WHERE Logtime BETWEEN ( SELECT val FROM params WHERE name = 'fw_vondat' )
                  AND ( SELECT val FROM params WHERE name = 'fw_bisdat' )

But if you are going to doing that frequently for many queries it's probably best to just create a table in Exasol that acts as a cache and which you can periodically re-create:

CREATE OR REPLACE TABLE params AS SELECT * FROM (
  IMPORT FROM JDBC AT mysql_connection STATEMENT '
        SELECT pParName AS name, pParValue AS val
        FROM tbl_parameter
        WHERE pParName IN ("fw_vondat","fw_bisdat")
') WITH DATA
;

SELECT ID, events, Logtime 
FROM tbl_data 
WHERE Logtime BETWEEN ( SELECT val FROM params WHERE name = 'fw_vondat' )
                  AND ( SELECT val FROM params WHERE name = 'fw_bisdat' )
;   

Regards,

Peter

View solution in original post

4 REPLIES 4

PeterK
Xpert

Hi @wooper ,

Exasol doesn't support variables of that sort but you can write them as subqueries: 

select ID, events, Logtime from tbl_data where Logtime between
   (select pParValue FROM tbl_parameter where pParName = 'fw_vondat') and
   (select pParValue FROM tbl_parameter where pParName = 'fw_bisdat');

I created an idea for session-scoped variables some while ago ( https://community.exasol.com/t5/ideas/pass-arbitrary-session-variables-into-virtual-schemas/idi-p/41... )...maybe you could vote for it 😉

Best,

Peter

wooper
Contributor

Hi @PeterK ,

thanks' for your answer. I tried to keep my example very simple. But now I have to go in detail for understanding. I will receive the parameters from a mysql db an analyze it in exasol. Your solution will work but will not be performant as ist could be.

CREATE OR REPLACE CONNECTION mysql_connection
       TO 'jdbc:mysql://127.0.0.1:3306/exampledb'
       USER 'username' IDENTIFIED BY 'password';   


select ID, events, Logtime from tbl_data where Logtime between
		(SELECT FLT_VONDAT from (import from JDBC at mysql_connection statement 'SELECT (select pParValue  FROM tbl_parameter  where pParName = "fw_vondat") as FLT_VONDAT ; ')) 
		and
		(SELECT FLT_BISDAT from (import from JDBC at mysql_connection statement 'SELECT (select pParValue  FROM tbl_parameter  where pParName = "fw_bisdat") as FLT_BISDAT ; '))
;   

 And as you can see the code is complexer as it could be.The idea is to read the parameters once an use ist for more queries. Do you know a solution for that too ?

thanks!

 

PeterK
Xpert

Hi @wooper,

If you want to use the parameters multiple times in the same query you could import them in a CTE (WITH clause) to avoid multiple connections to MySQL:

WITH params AS ( SELECT * FROM (
  IMPORT FROM JDBC AT mysql_connection STATEMENT '
        SELECT pParName AS name, pParValue AS val
        FROM tbl_parameter
        WHERE pParName IN ("fw_vondat","fw_bisdat")
'))
SELECT ID, events, Logtime 
FROM tbl_data 
WHERE Logtime BETWEEN ( SELECT val FROM params WHERE name = 'fw_vondat' )
                  AND ( SELECT val FROM params WHERE name = 'fw_bisdat' )

But if you are going to doing that frequently for many queries it's probably best to just create a table in Exasol that acts as a cache and which you can periodically re-create:

CREATE OR REPLACE TABLE params AS SELECT * FROM (
  IMPORT FROM JDBC AT mysql_connection STATEMENT '
        SELECT pParName AS name, pParValue AS val
        FROM tbl_parameter
        WHERE pParName IN ("fw_vondat","fw_bisdat")
') WITH DATA
;

SELECT ID, events, Logtime 
FROM tbl_data 
WHERE Logtime BETWEEN ( SELECT val FROM params WHERE name = 'fw_vondat' )
                  AND ( SELECT val FROM params WHERE name = 'fw_bisdat' )
;   

Regards,

Peter

wooper
Contributor

Thank's very much