MQT create issue

[JBaileys]

trying to create a MQT and it fails with error:

During SQL processing it returned:
SQL0206N "f" is not valid in the context where it is used.
SQLSTATE=42703

create table user.ytable as (select PERIOD, a, b, c, D, e sum(f) as sum_f, min (f) as min_f, g
from USER.yraw
group by grouping sets
(
(a),
(a, b)
)
)
data initially deferred refresh immediate

 

[MarcLodge]

In your example you've not got a comma in the following statement:
create table user.ytable as (select PERIOD, a, b, c, D, e [COLOR=red yellow] , [/color] sum(f) as sum_f, min (f) as min_f, g

Is this just a typo?

Marc

 

[JBaileys]

Marc,

Good catch. My query does have the comma in it. Any other ideas?

Thanks,

-Kevin

 

[MarcLodge]

As I see it, there's two parts to the SQL, the internal SELECT and the external CREATE. In order to eliminate one or other parts, I would take the

select PERIOD, a, b, c, D, e sum(f) as sum_f, min (f) as min_f, g
from USER.yraw
group by grouping sets

and run it on it's own outside of the CREATE. If it works, then you know it's something to do with the interaction between the CREATE and possibly the SUM statement. If it doesn't work, then you can work on fixing it without having to worry about the added complication of the CREATE.

Marc

 

[JBaileys]

changed the statement from:
data initially deferred refresh immediate

to:
data initially deferred refresh deferred

and everything worked fine. Wondering if there is a problem with the refresh or some crazy setting on DB2.

 

[JBaileys]

Why would refresh immediate option not be available on my system? Is there anything that I can review?