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.
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.