How to declare a variable in denodo?

Question

There is a question related to this topic but is not the same that I am going to ask, I need to do something similar as we do in SQL but this time in Denodo.

This is in SQL:

DECLARE @curr varchar (10);
SET @curr = 'USD;

SELECT
Country,
Currency
FROM
Currencies
WHERE
Currency = @curr;

I have tried something like this in Denodo

SELECT
Country,
Currency
FROM
Currencies
WHERE
Currency = GETVAR('curr', 'VARCHAR', 'USD');

But is not showing results. Does anyone know how can we do something similar to SQL variable declaration in Denodo?

Answer 1

Use function GETVAR('variable_name', 'type', default_value) in your query.

Then declare it in the CONTEXT statement: CONTEXT('VAR variable_name' = literal)

For example

SELECT * 
FROM (
    SELECT 1 FROM DUAL()
    UNION ALL 
    SELECT 2 FROM DUAL()
    UNION ALL 
    SELECT 3 FROM DUAL()
) 

WHERE field1 > GETVAR('a', 'int', 3)
ORDER BY  field1
CONTEXT('VAR a' = 1)

Answer 2

The error is caused because of the wrong data type You can use 'text' instead of using 'varchar' This change will save your query :)

Here is a sample usage

select 
* 
from
storm_storm_t001l
where
werks = GETVAR('werks', 'text', '1331')
;