Wow thanks so much, just with the better written query, it has made
it all work :) I'll go ahead now and optimise the rest, but thanks a
lot to you both...

2 weeks of query writing is now not a waste of time!


Thanks,
Cameron

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

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

>
> HTH,
> Set
>
> select id.ship_date,
> COALESCE(ch10.exchange_rate, 1), id.customer_price / COALESCE

(ch.exchange_rate, 1), id.customer_price,

> CAST(COALESCE(pd.vendor_price,id.unit_cost),
> (((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')
>