Could anyone tell me what's wrong with the fullselect in the following DDL to create a materialized query table with the refresh immediate option.

I get the following error message:

SQL20058N The fullselect specified for the materialized query table
"PPVUSER.CHECK_COUNT_BY_SIZE_MQT" is not valid. SQLSTATE=428EC

It works if I use the refresh deferred option.

Thanks,

create table ppvuser.check_count_by_size_mqt as (
select
'1' as start_range,
'100' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 0 and total <= 100
group by
month(end_time), year(end_time)
union
select
'101' as start_range,
'200' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 100 and total <= 200
group by
month(end_time), year(end_time)
union
select
'201' as start_range,
'300' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 200 and total <= 300
group by
month(end_time), year(end_time)
union
select
'301' as start_range,
'400' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 300 and total <= 400
group by
month(end_time), year(end_time)
union
select
'401' as start_range,
'500' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 400 and total <= 500
group by
month(end_time), year(end_time)
union
select
'501' as start_range,
'600' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 500 and total <= 600
group by
month(end_time), year(end_time)
union
select
'601' as start_range,
'700' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 600 and total <= 700
group by
month(end_time), year(end_time)
union
select
'701' as start_range,
'800' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 700 and total <= 800
group by
month(end_time), year(end_time)
union
select
'801' as start_range,
'900' as end_range,
month(end_time) as month,
year(end_time) as year,
count(payrun_id) as num_psids,
sum(total) as total,
count(total) as count_total,
sum(regular) as regular,
count(regular) as count_regular,
sum(additional) as additional,
count(additional) as count_additional,
sum(bonus) as bonus,
count(bonus) as count_bonus,
sum(vacation) as vacation,
count(vacation) as count_vacation,
sum(xpresscheck) as xpresscheck,
count(xpresscheck) as count_xpresscheck,
sum(manual) as manual,
count(manual) as count_manual,
sum(autovoid) as autovoid,
count(autovoid) as count_autovoid,
sum(void) as void,
count(void) as count_void,
count(*) as count
from
latest_check_count_view
where
end_time is not null and
total > 800 and total <= 900
group by
month(end_time), year(end_time)
)
Data Initially Deferred
Refresh immediate
Maintained by system
IN USERSPACE1
;