Hi There, I have this view that is a series of unions that is pulling
specific data out to calculate profits.

There is one query that is causing firebird to fall over, the reasons
I do not know..

Below is the view that works. Further down there is a small view that
is the one that is causing the problem. As soon as i do a union with
this view (or put the sql directly in this initial view) it crashes
the firebird service.


/* View: VW_PROFIT_MARGINS, Owner: SYSDBA */

CREATE VIEW "VW_PROFIT_MARGINS" (
"ship_date",
"usd_inv_exchangerate",
"au_unit_sale",
"unit_sale",
"unit_cost",
"audmargin",
"usdmargin",
"sale_currency",
"cost_currency",
"unit_commision",
"invoice_date",
"qty_ship",
"unit_freight",
"exchange_rate",
"ind_auto_key",
"sale_type",
"so_number",
"sod_auto_key",
"invc_number",
"div_auto_key",
"division_name"
) AS











select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(COALESCE(po_detail.vendor_price,0) as NUMERIC(15,4)) as unit_cost,
((invc_detail.customer_price - COALESCE(po_detail.vendor_price,0) -
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) -
COALESCE(cast(cq_udf_003 as float),0)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship)) /
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as audmargin,
(invc_detail.customer_price - COALESCE(po_detail.vendor_price,0) -
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) -
COALESCE(cast(cq_udf_003 as float),0)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
usdmargin,
c2.currency_code as sale_currency,
currency.currency_code as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
qty_ship,
COALESCE(CAST(cq_udf_003 as float),0) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
'b' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join parts_master on parts_master.pnm_auto_key =
so_detail.pnm_auto_key
left outer join cq_detail on cq_detail.cqd_auto_key =
so_detail.cqd_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
inner join stock_reservations on stock_reservations.sod_auto_key =
so_detail.sod_auto_key
inner join stock on stock.stm_auto_key = stock_reservations.stm_auto_key
left outer join invc_detail on invc_detail.ind_auto_key =
stock_reservations.ind_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
inner join po_detail on po_detail.pod_auto_key = stock.pod_auto_key
inner join po_header on po_header.poh_auto_key = po_detail.poh_auto_key
inner join currency on currency.cur_auto_key = po_header.cur_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
where so_detail.pod_link = 'T'
and invc_detail.ind_auto_key is not null
and so_detail.route_code not in ('X','J','M','F','E')
and invc_header.cur_auto_key = 10
and po_header.cur_auto_key = 10

union all

select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(po_detail.vendor_price AS NUMERIC(15,4)) as unit_cost,
(((invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
/ invc_detail.exchange_rate) - po_detail.unit_cost) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
aumargin,
(((invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
/ invc_detail.exchange_rate) - po_detail.unit_cost) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) *
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usdmargin,
c2.currency_code as sale_currency,
currency.currency_code as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
qty_ship,
COALESCE(CAST(cq_udf_003 as float),0) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
'b' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join parts_master on parts_master.pnm_auto_key =
so_detail.pnm_auto_key
left outer join cq_detail on cq_detail.cqd_auto_key =
so_detail.cqd_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
inner join stock_reservations on stock_reservations.sod_auto_key =
so_detail.sod_auto_key
inner join stock on stock.stm_auto_key = stock_reservations.stm_auto_key
left outer join invc_detail on invc_detail.ind_auto_key =
stock_reservations.ind_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
inner join po_detail on po_detail.pod_auto_key = stock.pod_auto_key
inner join po_header on po_header.poh_auto_key = po_detail.poh_auto_key
inner join currency on currency.cur_auto_key = po_header.cur_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
where so_detail.pod_link = 'T'
and invc_detail.ind_auto_key is not null
and so_detail.route_code not in ('X','J','M','F','E')
and invc_header.cur_auto_key <> 10 or po_header.cur_auto_key <> 10

union all

select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(COALESCE(po_detail.vendor_price,0) AS NUMERIC(15,4)) as unit_cost,
((invc_detail.customer_price - COALESCE(po_detail.vendor_price,0) -
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) -
COALESCE(cast(cq_udf_003 as float),0)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship)) /
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as audmargin,
(invc_detail.customer_price - COALESCE(po_detail.vendor_price,0) -
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) -
COALESCE(cast(cq_udf_003 as float),0)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
usdmargin,
c2.currency_code as sale_currency,
currency.currency_code as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
qty_ship,
COALESCE(CAST(cq_udf_003 as float),0) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
's' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join parts_master on parts_master.pnm_auto_key =
so_detail.pnm_auto_key
left outer join cq_detail on cq_detail.cqd_auto_key =
so_detail.cqd_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
left outer join exchange on exchange.sod_auto_key = so_detail.sod_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
left outer join stock_reservations on stock_reservations.sod_auto_key
= so_detail.sod_auto_key
left outer join stock on stock.stm_auto_key =
stock_reservations.stm_auto_key
left outer join invc_detail on invc_detail.ind_auto_key =
stock_reservations.ind_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
left outer join po_detail on po_detail.pod_auto_key = stock.pod_auto_key
left outer join po_header on po_header.poh_auto_key =
po_detail.poh_auto_key
left outer join currency on currency.cur_auto_key = po_header.cur_auto_key
where exchange.exc_auto_key is null
and (so_detail.rod_link is null)
and stock.stm_lot is null
and so_detail.pod_link is null
and invc_detail.ind_auto_key is not null
and so_detail.route_code not in ('X','J','M','F')
and po_header.cur_auto_key = 10 and invc_header.cur_auto_key = 10

union all

select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(COALESCE(po_detail.vendor_price,invc_detail.unit_cost) AS
NUMERIC(15,4)) as unit_cost,
(((invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
/ (select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = invc_header.cur_auto_key
))) - COALESCE(po_detail.unit_cost,invc_detail.unit_cost)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
audmargin,
(((invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
/ (select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = invc_header.cur_auto_key
))) - COALESCE(po_detail.unit_cost,invc_detail.unit_cost)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) *
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usdmargin,
c2.currency_code as sale_currency,
COALESCE(currency.currency_code,'AUD') as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
qty_ship,
COALESCE(CAST(cq_udf_003 as float),0) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
's' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join parts_master on parts_master.pnm_auto_key =
so_detail.pnm_auto_key
left outer join cq_detail on cq_detail.cqd_auto_key =
so_detail.cqd_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
left outer join exchange on exchange.sod_auto_key = so_detail.sod_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
left outer join stock_reservations on stock_reservations.sod_auto_key
= so_detail.sod_auto_key
left outer join stock on stock.stm_auto_key =
stock_reservations.stm_auto_key
left outer join invc_detail on invc_detail.ind_auto_key =
stock_reservations.ind_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
left outer join po_detail on po_detail.pod_auto_key = stock.pod_auto_key
left outer join po_header on po_header.poh_auto_key =
po_detail.poh_auto_key
left outer join currency on currency.cur_auto_key = po_header.cur_auto_key
where exchange.exc_auto_key is null
and (so_detail.rod_link is null)
and stock.stm_lot is null
and so_detail.pod_link is null
and invc_detail.ind_auto_key is not null
and so_detail.route_code not in ('X','J','M','F')
and (po_header.cur_auto_key <> 10 or po_header.cur_auto_key is null or
invc_header.cur_auto_key <> 10)

union all


select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(COALESCE(ro_detail.foreign_misc + ro_detail.foreign_labor +
ro_detail.foreign_parts,0) as NUMERIC(15,4)) as unit_cost,
((invc_detail.customer_price - COALESCE(ro_detail.foreign_misc +
ro_detail.foreign_labor + ro_detail.foreign_parts,0) -
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) -
COALESCE(cast(cq_udf_003 as float),0)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship)) /
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as audmargin,
((invc_detail.customer_price - COALESCE(ro_detail.foreign_misc +
ro_detail.foreign_labor + ro_detail.foreign_parts,0) -
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) -
COALESCE(cast(cq_udf_003 as float),0)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship)) as
usdmargin,
c2.currency_code as sale_currency,
currency.currency_code as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
qty_ship,
COALESCE(CAST(cq_udf_003 as float),0) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
'r' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join parts_master on parts_master.pnm_auto_key =
so_detail.pnm_auto_key
left outer join cq_detail on cq_detail.cqd_auto_key =
so_detail.cqd_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
left outer join stock_reservations on stock_reservations.sod_auto_key
= so_detail.sod_auto_key
left outer join stock on stock.stm_auto_key =
stock_reservations.stm_auto_key
left outer join invc_detail on invc_detail.ind_auto_key =
stock_reservations.ind_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
left outer join ro_detail on ro_detail.rod_auto_key = stock.rod_auto_key
left outer join ro_header on ro_header.roh_auto_key =
ro_detail.roh_auto_key
left outer join currency on currency.cur_auto_key = ro_header.cur_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
where so_detail.rod_link = 'T'
and stock.stm_lot is null
and invc_detail.ind_auto_key is not null
and so_detail.route_code not in ('X','J','M','F','E')
and invc_header.cur_auto_key = 10
and ro_header.cur_auto_key = 10
union all

select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(COALESCE(ro_detail.foreign_misc + ro_detail.foreign_labor +
ro_detail.foreign_parts,0) as NUMERIC(15,4)) as unit_cost,
(((invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
/ invc_detail.exchange_rate) - (ro_detail.labor_cost +
ro_detail.misc_cost + ro_detail.parts_cost)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
aumargin,
(((invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
/ invc_detail.exchange_rate) - (ro_detail.labor_cost +
ro_detail.misc_cost + ro_detail.parts_cost)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) *
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usdmargin,
c2.currency_code as sale_currency,
currency.currency_code as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
qty_ship,
COALESCE(CAST(cq_udf_003 as float),0) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
'r' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join parts_master on parts_master.pnm_auto_key =
so_detail.pnm_auto_key
left outer join cq_detail on cq_detail.cqd_auto_key =
so_detail.cqd_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
left outer join stock_reservations on stock_reservations.sod_auto_key
= so_detail.sod_auto_key
left outer join stock on stock.stm_auto_key =
stock_reservations.stm_auto_key
left outer join invc_detail on invc_detail.ind_auto_key =
stock_reservations.ind_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
inner join ro_detail on ro_detail.sod_auto_key = so_detail.sod_auto_key
left outer join ro_header on ro_header.roh_auto_key =
ro_detail.roh_auto_key
left outer join currency on currency.cur_auto_key = ro_header.cur_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
where so_detail.rod_link = 'T'
and stock.stm_lot is null
and invc_detail.ind_auto_key is not null
and so_detail.route_code not in ('X','J','M','F','E')
and (invc_header.cur_auto_key <> 10 or ro_header.cur_auto_key <> 10)

union all


select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
case when exch_cost_method = 1 then
CAST(COALESCE(cast(cq_udf_004 as float),0) as NUMERIC(15,4))
else
CAST(0 as NUMERIC(15,4)) end as unit_cost,
case when exch_cost_method = 1 then
(invc_detail.customer_price - COALESCE(cast(cq_udf_004 as float),0) -
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) -
COALESCE(cast(cq_udf_003 as float),0)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship)
else
(invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
* COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) end /
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as audmargin,
case when exch_cost_method = 1 then
(invc_detail.customer_price - COALESCE(cast(cq_udf_004 as float),0) -
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) -
COALESCE(cast(cq_udf_003 as float),0)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship)
else
(invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
* COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) end
as usdmargin,
c2.currency_code as sale_currency,
COALESCE(currency.currency_code,c2.currency_code) as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
qty_ship,
COALESCE(CAST(cq_udf_003 as float),0) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
'e' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from exchange
inner join so_detail on so_detail.sod_auto_key = exchange.sod_auto_key
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join parts_master on parts_master.pnm_auto_key =
so_detail.pnm_auto_key
left outer join cq_detail on cq_detail.cqd_auto_key =
so_detail.cqd_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
left outer join stock_reservations on stock_reservations.sod_auto_key
= so_detail.sod_auto_key
left outer join stock on stock.stm_auto_key =
stock_reservations.stm_auto_key
left outer join invc_detail on invc_detail.ind_auto_key =
stock_reservations.ind_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
left outer join po_detail on po_detail.pod_auto_key = stock.pod_auto_key
left outer join po_header on po_header.poh_auto_key =
po_detail.pod_auto_key
left outer join currency on currency.cur_auto_key = po_header.cur_auto_key
where invc_detail.ind_auto_key is not null and so_detail.route_code <> 'X'
and so_detail.route_code not in ('X','J','M','F')
union all

select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(invc_detail.unit_cost * so_detail.exchange_rate AS NUMERIC(15,4))
as unit_cost,
((invc_detail.unit_price - invc_detail.unit_cost) *
invc_detail.qty_ship) as audmargin,
((invc_detail.unit_price - invc_detail.unit_cost) *
invc_detail.qty_ship) * (select exchange_rate from cur_history where
cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2 where
ch2.eff_date <= invc_detail.invoice_date and cur_auto_key = 10)) as
usdmargin,
c2.currency_code as sale_currency,
c2.currency_code as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(invc_detail.qty_ship,0) as qty_ship,
CAST(0 as float) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
'c' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
left outer join invc_detail on invc_detail.sod_auto_key =
so_detail.sod_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
where
invc_detail.ind_auto_key is not null
and so_detail.route_code in ('X','J','M','F')
and c2.cur_auto_key <> 10

union all

select invc_detail.ship_date,
(select exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as usd_inv_exchangerate,
invc_detail.customer_price / (select exchange_rate from cur_history
where cuh_auto_key = (select max(cuh_auto_key) from cur_history ch2
where ch2.eff_date <= invc_detail.invoice_date and cur_auto_key =
invc_header.cur_auto_key )) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(invc_detail.unit_cost * so_detail.exchange_rate AS NUMERIC(15,4))
as unit_cost,
((invc_detail.customer_price - (invc_detail.unit_cost *
so_detail.exchange_rate)) * invc_detail.qty_ship) / (select
exchange_rate from cur_history where cuh_auto_key = (select
max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)) as audmargin,
(invc_detail.customer_price - (invc_detail.unit_cost *
so_detail.exchange_rate)) * invc_detail.qty_ship as usdmargin,
c2.currency_code as sale_currency,
c2.currency_code as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(invc_detail.qty_ship,0) as qty_ship,
CAST(0 AS float) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
'c' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
left outer join invc_detail on invc_detail.sod_auto_key =
so_detail.sod_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
where
invc_detail.ind_auto_key is not null
and so_detail.route_code in ('X','J','M','F')
and c2.cur_auto_key = 10;



I have a separate view, that has the query that i know makes firebird
die, if included in the view above.

This is the view:


/* View: VW_PROFIT_MARGINS_STOCK_LOT, Owner: SYSDBA */

CREATE VIEW "VW_PROFIT_MARGINS_STOCK_LOT" (
"ship_date",
"usd_inv_exchangerate",
"au_unit_sale",
"unit_sale",
"unit_cost",
"audmargin",
"usdmargin",
"sale_currency",
"cost_currency",
"unit_commision",
"invoice_date",
"qty_ship",
"unit_freight",
"exchange_rate",
"ind_auto_key",
"sale_type",
"so_number",
"sod_auto_key",
"invc_number",
"div_auto_key",
"division_name"
) AS




select invc_detail.ship_date,
COALESCE((select exchange_rate from cur_history where cuh_auto_key =
(select max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)),1) as
usd_inv_exchangerate,
invc_detail.customer_price / COALESCE((select exchange_rate from
cur_history where cuh_auto_key = (select max(cuh_auto_key) from
cur_history ch2 where ch2.eff_date <= invc_detail.invoice_date and
cur_auto_key = invc_header.cur_auto_key )),1) as au_unit_sale,
invc_detail.customer_price as unit_sale,
CAST(COALESCE(po_detail.vendor_price,invc_detail.unit_cost) AS
NUMERIC(15,4)) as unit_cost,
(((invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
/ COALESCE((select exchange_rate from cur_history where cuh_auto_key
= (select max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = invc_header.cur_auto_key
)),1)) - invc_detail.unit_cost) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
audmargin,
(((invc_detail.customer_price - COALESCE(agent_comm / 100 *
invc_detail.customer_price,0) - COALESCE(cast(cq_udf_003 as float),0))
/ COALESCE((select exchange_rate from cur_history where cuh_auto_key
= (select max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = invc_header.cur_auto_key
)),1)) - COALESCE(po_detail.unit_cost,invc_detail.unit_cost)) *
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) *
COALESCE((select exchange_rate from cur_history where cuh_auto_key =
(select max(cuh_auto_key) from cur_history ch2 where ch2.eff_date <=
invc_detail.invoice_date and cur_auto_key = 10)),1) as usdmargin,
c2.currency_code as sale_currency,
COALESCE(currency.currency_code,'AUD') as cost_currency,
COALESCE(agent_comm / 100 * invc_detail.customer_price,0) as
unit_commision,
invc_detail.invoice_date,
COALESCE(stock_reservations.qty_invoiced,invc_detail.qty_ship) as
qty_ship,
COALESCE(CAST(cq_udf_003 as float),0) as unit_freight,
invc_detail.exchange_rate,
invc_detail.ind_auto_key,
's' as sale_type,
so_header.so_number,
so_detail.sod_auto_key,
invc_header.invc_number,
division.div_auto_key,
division.division_name
from so_detail
left outer join so_header on so_header.soh_auto_key =
so_detail.soh_auto_key
left outer join parts_master on parts_master.pnm_auto_key =
so_detail.pnm_auto_key
left outer join cq_detail on cq_detail.cqd_auto_key =
so_detail.cqd_auto_key
left outer join department on department.dpt_auto_key =
so_header.dpt_auto_key
left outer join division on division.div_auto_key =
department.div_auto_key
left outer join companies on companies.cmp_auto_key =
so_header.cmp_auto_key
left outer join exchange on exchange.sod_auto_key = so_detail.sod_auto_key
left outer join agents on agents.agn_auto_key = so_header.agn_auto_key
left outer join stock_reservations on stock_reservations.sod_auto_key
= so_detail.sod_auto_key
left outer join stock on stock.stm_auto_key =
stock_reservations.stm_auto_key
left outer join invc_detail on invc_detail.ind_auto_key =
stock_reservations.ind_auto_key
left outer join invc_header on invc_header.inh_auto_key =
invc_detail.inh_auto_key
left outer join currency c2 on c2.cur_auto_key = invc_header.cur_auto_key
left outer join po_detail on po_detail.pod_auto_key = stock.pod_auto_key
left outer join po_header on po_header.poh_auto_key =
po_detail.poh_auto_key
left outer join currency on currency.cur_auto_key = po_header.cur_auto_key
where exchange.exc_auto_key is null
and (stock.stm_lot is not null)
and invc_detail.ind_auto_key is not null
and so_detail.route_code not in ('X','J','M','F')
;


The view itself runs fine by itself.

But as soon as i Do an union with it, in the first view above, it
crashes firebird as soon as i run it.. (it does compile fine though)

Anything I could try to sort it out?

Thanks,
Cameron