How do I declare a variable in Netezza?
I have a Netezza query where I am referencing a couple of dates in a series of case statements. Instead of replacing all these dates every time I would like to declare a variable at the beginning and use it during the request. In SAS, I would 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,
...
source to share
Unfortunately, there are no procedural SQL extensions in Netezza that allow such variables to be used as part of the SQL language. Pure SQL solutions will include bugs like joining a CTE returning a single value. However, the NZSQL CLI allows session variables, just like the Aginity Workbench.
An example of using NZSQL. Note the output 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 automatically prompt for values when it sees $ var_name, but there is no function to hardcode that variable, at least as far as I know.
source to share