Re: [SQL] ERROR: DefineQueryRewrite: rule plan string too big.

At 23:45 +0300 on 23/07/1999, John M. Flinchbaugh wrote:

> create view allinfo as select b.name as building_name,b.description as
> building_description,b.location as building_location,b.address as
> building_address,f.location as floor,r.name as room_name,r.location as
> room_location,r.dept as room_department,rt.name as room_type,h.mac as
> mac,h.name as hardware_name,h.ip as ip,h.mutag as mutag,rack.location as
> rack,rack.description as rack_description,h.pos as pos,h.serial as
> serial,h.inuse as inuse,m.name as model_name,m.description as
> model_description,m.model as model,c.name as model_class,manf.name as
> manufacturer from building as b,floor as f,room as r,roomtype as
> rt,hardware as h,dept as d,model as m,class as c,manufacturer as manf,rack
> where b.number=f.building and f.number=r.floor and r.number=rack.room and
> rack.number=h.rack and m.class=c.number and m.manufacturer=manf.number and
> r.dept=d.number;

First, I do believe that in the FROM clause, you don't use "as" to create
table aliases. This is done only in the target list. For table aliases you
just put the new name:

FROM building b, floor f, room r, roomtype rt, hardware h, dept d etc.

Now, here is a (braindead) workaround which doesn't require you to plan a
lot, and may help you reduce this plan into two views.

View number 1 - this same view, only with short field names:

CREATE VIEW internal1 AS
SELECT b.name as f1, b.description as f2, ....
FROM ....
WHERE ....;

View number 2 - straight select from internal1, only renaming the fields to
their longer names:

CREATE VIEW allinfo AS
SELECT f1 as building_name, f2 as building_description, f3 as ...
FROM internal1;

This may get you just under the threshold and save you a lot of thinking.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma