DBeaver, How to declare variables and use them?

Question

i just want to know if it is possible to declare variables on the DBeaver´s sql editor and use them on a query

Answer 1

You have to enable variable processing in the "SQL Processing" settings of DBeaver -> Window -> Preferences -> Database -> Editors -> SQL Editor -> SQL Processing. There is a block on Parameters with settings you can change. See the Dynamic Parameter binding section on the wiki.

You should then be able to do:

@set date = '2019-10-09'

SELECT ${date}::DATE, ${date}::TIMESTAMP WITHOUT TIME ZONE

which produces:

| date       | timestamp           |
|------------|---------------------|
| 2019-10-09 | 2019-10-09 00:00:00 |

Answer 2

Yes you can, using :.

An example:

SELECT * FROM "SYSIBM".SYSDUMMY1
WHERE IBMREQD = :YOUR_VARIABLE

Answer 3

Based on the incredibly helpful post from @nicoschl, here are a couple of minor improvements:

-- using declarations
@set datex_start = cast('2120-01-01' as date) as date_start;
-- datex_start is the var name
-- casting the value in the declaration saves us the work later
-- the var can be given a default fieldname (e.g. "date_start")

-- run as a standalone command since the subsequent SELECT statement doesn't return values when it's all run together

select 
    ${datex_start}
;

This will return a value "2120-01-01" with a fieldname of "date_start".