how to declare a variable in Netezza?

Question

I have a Netezza query where I reference a couple of dates in a series of case statements. Instead of replacing all of these dates every time I'd like to declaire a variable at the beginning and use that throughout the query. In SAS I'd do it like this:

%LET end_p = '31DEC2014'd;

proc sql;
create table want as
select distinct id,

sum(case when (INCUR_DT) >= (&end_p-30) 
    and ip_op_cd = 'IP'
    then net_allow_at else 0 end) as ip_d_30,

sum(case when (INCUR_DT) >= (&end_p-90) 
    and ip_op_cd = 'IP'
    then net_allow_at else 0 end) as ip_d_90,

sum(case when (INCUR_DT) >= (&end_p-180)    
    and ip_op_cd = 'IP'
    then net_allow_at else 0 end) as ip_d_180,
...

Answer 1

Unfortunately, there are no procedural SQL extensions in Netezza that allow you to employ variables like this as part of the SQL language itself. Purely SQL solutions would involve kludges such as joining to a CTE returning that one value. However, the NZSQL CLI does allow the use of session variables, as does Aginity Workbench.

An example using NZSQL. Note the escape of the inner single quotes to use the variable as a literal.

TESTDB.ADMIN(ADMIN)=> \set TVAR '\'foo\''
TESTDB.ADMIN(ADMIN)=> select :TVAR;
 ?COLUMN?
----------
 foo
(1 row)
TESTDB.ADMIN(ADMIN)=> create table test_table (col1 bigint);
CREATE TABLE
TESTDB.ADMIN(ADMIN)=> insert into test_table values (123);
INSERT 0 1
TESTDB.ADMIN(ADMIN)=> \set TCOL 'COL1'
TESTDB.ADMIN(ADMIN)=> select :TCOL from test_table;
 COL1
------
  123
(1 row)

Aginity will auto-prompt for a values when it see $var_name, but there's no functionality to hard-code that variable definition, at least as far as I know.