Subject RE: [firebird-support] Crashing Firebird 1.5 Server with a query
Author Svein Erling Tysvær
>My best guess is that you are tipping the limits = 64 Kb for a single statement (including one you crystallise as a view) and >48 Kb for a plan.

I think the number of characters in his first and second view taken together is about 32Kb.

>>Anything I could try to sort it out?


The query in your last view can at least be reduced from 3718 characters to 2542 characters by using aliases, removing LEFT where the join actually is an inner join, OUTER everywhere (LEFT JOIN is the same as LEFT OUTER JOIN), and using LEFT JOIN rather than repeated subselects. As a positive side effect to these changes, I think the query becomes more readable. Below is my attempt, though note that I generally added aliases, even where you hadn't included a table name. Not knowing your table structure, there will be errors - e.g. I've no idea which table cq_udf_003 belongs to and just guessed so_detail.


select id.ship_date,
COALESCE(ch10.exchange_rate, 1), id.customer_price / COALESCE(ch.exchange_rate, 1), id.customer_price,
(((id.customer_price - COALESCE(sd.agent_comm / 100 * id.customer_price,0) - COALESCE(cast(sd.cq_udf_003 as float),0)) / COALESCE(ch.exchange_rate, 1)) - id.unit_cost) * COALESCE(sr.qty_invoiced,id.qty_ship), (((id.customer_price - COALESCE(id.agent_comm / 100 * id.customer_price,0) - COALESCE(cast(sd.cq_udf_003 as float),0)) / COALESCE(ch.exchange_rate, 1)) - COALESCE(pd.unit_cost,id.unit_cost)) * COALESCE(sr.qty_invoiced,id.qty_ship) * COALESCE(ch.exchange_rate, 1), c2.currency_code, COALESCE(cu.currency_code,'AUD'), COALESCE(sd.agent_comm / 100 * id.customer_price,0), id.invoice_date, COALESCE(sr.qty_invoiced, id.qty_ship), COALESCE(CAST(sd.cq_udf_003 as float),0), id.exchange_rate, id.ind_auto_key, 's', sh.so_number, sd.sod_auto_key, ih.invc_number, di.div_auto_key, di.division_name
from so_detail sd
join stock_reservations sr on sr.sod_auto_key = sd.sod_auto_key
join stock s on s.stm_auto_key = sr.stm_auto_key
join invc_detail id on id.ind_auto_key = sr.ind_auto_key
left join so_header sh on sh.soh_auto_key = sd.soh_auto_key
left join parts_master pm on pm.pnm_auto_key = sd.pnm_auto_key
left join cq_detail cd on cd.cqd_auto_key = sd.cqd_auto_key
left join department d on d.dpt_auto_key = sh.dpt_auto_key
left join division di on di.div_auto_key = d.div_auto_key
left join companies c on c.cmp_auto_key = sh.cmp_auto_key
left join exchange e on e.sod_auto_key = sd.sod_auto_key
left join agents a on a.agn_auto_key = sh.agn_auto_key
left join invc_header ih on ih.inh_auto_key = id.inh_auto_key
left join currency c2 on c2.cur_auto_key = ih.cur_auto_key
left join po_detail pd on pd.pod_auto_key = s.pod_auto_key
left join po_header ph on ph.poh_auto_key = pd.poh_auto_key
left join currency cu on cu.cur_auto_key = ph.cur_auto_key
left join cur_history ch on ch.eff_date <= id.invoice_date and ch.cur_auto_key = ih.cur_auto_key
and not exists(select * from cur_history ch2 where ch2.eff_date <= id.invoice_date and ch2.cur_auto_key = ih.cur_auto_key and ch2.cuh_auto_key > ch.cuh_auto_key)
left join cur_history ch10 on ch10.eff_date <= id.invoice_date and ch10.cur_auto_key = 10
and not exists(select * from cur_history ch2 where ch2.eff_date <= id.invoice_date and ch2.cur_auto_key = 10 and ch2.cuh_auto_key > ch10.cuh_auto_key)
where e.exc_auto_key is null
and (s.stm_lot is not null)
and id.ind_auto_key is not null
and sd.route_code not in ('X','J','M','F')