如何在SELECT中使用变量？ [英] How to use variables in SELECT?

问题描述

我使用IBM®Data Studio V4.1.0.1，DB2 v10.5。

I use IBM® Data Studio V4.1.0.1, DB2 v10.5.

这是我的存储过程。

CREATE PROCEDURE test ()
    DYNAMIC RESULT SETS 1
P1: BEGIN
    DECLARE ageInterval INTEGER;

    SELECT (MAX("age")-min("age"))/5
    INTO ageInterval 
    FROM "Schema1"."adult";   


create view "DiscreteTrain" as 
select 
"age"/ageInterval,
"income"
from "Schema1"."train";

END P1

当我部署它，数据工作室说 DB2ADMIN.TEST：15：AGEINTERVAL在使用它的上下文中无效。 SQLCODE = -206，SQLSTATE = 42703，DRIVER = 3.67.28

When I deploy it, data studio says DB2ADMIN.TEST: 15: "AGEINTERVAL" is not valid in the context where it is used. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.67.28

如何使用变量？

推荐答案

您不能在视图中使用此变量。根据文档 ：

You cannot use this variable in a view. According to the documentation:

全局变量可以在允许表达式为
的任何上下文中使用。与主变量不同，全局变量可以在
CREATE VIEW语句中使用。

Global variables can be used in any context where an expression is allowed. Unlike a host variable, a global variable can be used in a CREATE VIEW statement.

所以，你有一些选项。您可以使用创建变量将变量切换到全局变量。或者，您可以每次进行计算：

So, you have some options. You can switch the variable to a global variable by using create variable. Or, you can do the calculation each time:

create view "DiscreteTrain" as 
    select  "age"/a.ageInterval as MyAge, "income"
    from "Schema1"."train" cross join
         (SELECT (MAX("age")-min("age"))/5 as ageInterval
          FROM "Schema1"."adult"
         ) a;

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