Setting a variable in ANSI SQL

Question

I am new to SQL and have been searching for a way to set variables in ANSI SQL. I have this:

select * from table1
where first_date > '2014-01-01'
and where second_date = '2014-01-01'
and where third_date < '2014-01-01'

but I am hoping for something like:

set x = '2010-12-31'
select * from table1
where first_date > x
and where second_date = x
and where third_date < x

I read about stored procedures but it seems like overkill for something so seemingly simple. I'm running on Netezza but I'd like a general solution that can also work on other databases.

Calcar

Answer 1

Standard (ANSI) SQL does not have variables. But what you can do in standard SQL is use a common table expression to only have the value once.

The following is ANSI SQL:

with data (the_date) as (
    values (date '2014-01-01')
)
select *
from table1
where first_date > (select the_date from data)
  and second_date = (select the_date from data)
  and third_date < (select the_date from data);

The above would work on most DBMS. Not all of them support the values clause like that, but that can usually be worked around using a plain select statement instead.

As I have never used Netezza I have no idea if it supports the row constructor (the values clause) or common table expressions (the with clause)

Also some SQL clients offer the ability to define variables that are replaced before the SQL is actually sent to the database server.

Nudi

CTEs work in Netezza, but instead of "values (date '2014-01-01')" you would use "select '2014-01-01'::DATE" – Tristantristas

Answer 2

a_horse_with_no_name's solution is the only pure SQL solution I know that doesn't go into procedural SQL extenion territory. There is another solution that is not strictly what you asked for as it is specific to the NZSQL CLI only, but you can use variables there like this.

TESTDB.ADMIN(ADMIN)=> \set x '\'2014-01-01\''

TESTDB.ADMIN(ADMIN)=>
SELECT *
FROM table1
WHERE first_date < :x
AND second_date  = :x
AND third_date   = :x;

Tristantristas

That's exactly what I was referring to when I wrote "some SQL clients offer the ability to define variables" – Nudi