如何在Netezza中声明变量? [英] how to declare a variable in Netezza?

问题描述

我有一个Netezza查询，其中我在一系列case语句中引用了几个日期.而不是每次我都想在开始时将一个变量置为一个变量并在整个查询中都使用它时，而不是替换所有这些日期.在SAS中，我会这样:

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,
...

推荐答案

不幸的是，Netezza中没有过程SQL扩展，因此您无法将此类变量用作SQL语言本身的一部分.纯粹的SQL解决方案将涉及一些麻烦，例如加入CTE并返回该值.但是，NZSQL CLI确实允许使用会话变量，Aginity Workbench也是如此.

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.

使用NZSQL的示例.请注意，将单引号内的转义符用作将变量用作文字.

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)

当看到$ var_name时，Aginity会自动提示输入一个值，但是至少就我所知，没有功能可以硬编码该变量定义.

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.

这篇关于如何在Netezza中声明变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！