CODEWITHSUNDEEP
sqlstored-proceduresviewdb2global-variables
user746461
user746461

Reputation:

How to use variables in SELECT?

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

This is my stored procedure.

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

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

How should I use the variable?

Upvotes: 0

Views: 608

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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

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;

Upvotes: 1

Related Questions