UNION limit

UDA
N/A

UNION limit

Hi,

I've got a situation as follows...a single view containing multiple views with some filter condition on each child view.

replace view main_view as
select * from view_1
UNION ALL
select * from view_2
....
and so on till
UNION ALL
select * from view_15

and the definition of each view is something like this...

replace view view_1 as
select 1 as id, col1, col2, sum(col20) from view_x
where id = 1
group by col1, col2

replace view view_2 as
select 2 as id, col2, col3, sum(col67) from view_x
where id = 2
group by col2, col3

and so on...

basically each view i.e. from view_1..view_x they all access the same view but they group differently to suit the requirement.

The trouble I'm having is teradata doesn't accept more that 15 UNION ALLs for defining my main_view. I've tried splitting 15 views into 2 or 3 groups but still the same problem. It is giving me an error of 3710: Insufficient memory to p**** this request, during Resolver phase. Is the only solution to increase the memory??

Is there any other way around this??? My requirement is based on the ID value I get from my application, I need to run only that specific view that will matche the given ID. Therefore, if the application send 2, then only view_2 should be executed.

Please let me know if I need to explain the problem in further detail...All suggestions welcome.

Regards,
AnKo

1 REPLY
Enthusiast

Re: UNION limit

We had a similar issue in our warehouse and this is what we did to solve.
Modify the 'MaxP****TreeSegs' (actully the word in starts is P a r s e with no spaces)setting from 1000 to 3000.
we had this at 2000 and then the query failed, after setting it to 3000 it worked fine.

Try it and see it might work.