I'm having a problem with stack overflows which I really don't
understand.

I have a view (VIEW 1 - See after Sig) which provides the basic data
for a secondary view (VIEW 2 - See after Sig).

- I assume compounded views of this nature are allowed in IB6 -
certainly experimentation seem to support this.

Both views work quite satisfactorily in the interactive sql window of
IB Console.

Both views work in IB_WiSQL - IF I USE --
Browse/Relations/"select_my_view"/Data.

If I try to get at VIEW 2 using the query forms section of ib_Wisql I
SOMETIMES get a stack overflow. VIEW 1 works all the time

If I try to attack VIEW 2 with an IB_Query I ALWAYS get a Stack
overflow or Illegal operation.

Thoughts that occur to me -

1) The PLAN(see after sig) is too big.

2) I'm mis-using F_Truncate - One of its "nice" side effects is that
it appears to supply a value of zero when the "true" answer is null.
Maybe this little bit of cheating is causing my downfall

3) There's too much data - seems improbable - Even when it works
there are only 300 rows of data selected from a range of 2500 rows -
Tiny I'd have thought.

The truth of the matter is that I only want VIEW 2 but this
compounded manner seems to run 20 - 25% faster than creating
a "single" view. Anyway - even as a single view I also get stack
overflows.

Can anyone help please?


Regards John

VIEW 1 is as follows

CREATE VIEW V_EST_V_aCT_COMM_JOBS_Pt_1
(JOB,CO,EXEC,CLIENT,TITLE,EST_SALES,EST_INCOME,WIP,SALES,DPXS,EXECXS,F
IELDxS,OTHERXS,times)

AS
select
j.JOB_ID as job
, j.JOB_CO as co
, (select e.exec_last_name from execs e where j.exec_id = e.exec_id)
as exec
, (select c.client_name from clients c where j.client_id =
c.client_id) as client
, j.JOB_TITLE as title
, j.EST_SALES
, j.EST_INCOME
, f_truncate(j.wip_now) as wip
, f_truncate ((select sum(s.sales_amount) from sales s where j.job_id
= s.job_id and j.job_co = s.job_co)) as sales
, f_truncate((select sum(d.dp_exps_amount) from dp_exps d where
j.job_id = d.job_id and j.job_co = d.job_co)) as dpxS
, f_truncate((select sum(e.exec_exps_amount) from exec_exps e where
j.job_id = e.job_id and j.job_co = e.job_co)) as execxS
, f_truncate((select sum(f.field_exps_amount) from field_exps f where
j.job_id = f.job_id and j.job_co = f.job_co)) as fieldXS
, f_truncate((select sum(o.other_exps_amount) from other_exps o where
j.job_id = o.job_id and j.job_co = o.job_co)) as otherxS
, f_truncate((select sum(t.timesheet_amount) from timesheets t where
j.job_id = t.job_id and j.job_co = t.job_co)) as times

from jobs j

where j.job_commissioned_status = 'Yes' and j.has_more_income = 'Y';

commit;

VIEW 2 is as follows

create view V_EST_V_aCT_COMM_JOBS_Pt_2 (job ,co , exec , client ,
title , est_sales , sales , still_to_bill , est_income , adj_inc ,
still_to_earn)

as

SELECT v.JOB
, v.CO
, v.EXEC
, v.CLIENT
, v.TITLE
, v.EST_SALES
, v.SALES
, v.est_sales - v.sales as still_to_bill
, v.EST_INCOME
, v.sales - (v.dpxs + v.execxs + v.fieldxs + v.otherXs) + v.wip
as adj_inc
, v.est_income - (v.sales - (v.dpxs + v.execxs + v.fieldxs +
v.otherXs) + v.wip) as still_to_earn
FROM V_EST_V_ACT_COMM_JOBS_PT_1 v ;

THE PLAN is as follows

SALES INDEX (IDX_FK_SALES_V_JOBS))(SALES INDEX (IDX_FK_SALES_V_JOBS))
(DP_EXPS INDEX (IDX_FK_DP_EXPS_V_JOBS))(DP_EXPS INDEX
(IDX_FK_DP_EXPS_V_JOBS))(EXEC_EXPS INDEX (IDX_FK_EXEC_EXPS_V_JOBS))
(EXEC_EXPS INDEX (IDX_FK_EXEC_EXPS_V_JOBS))(FIELD_EXPS INDEX
(IDX_FK_FIELD_EXPS_V_JOBS))(FIELD_EXPS INDEX
(IDX_FK_FIELD_EXPS_V_JOBS))(OTHER_EXPS INDEX
(IDX_FK_OTHER_EXPS_V_JOBS))(OTHER_EXPS INDEX
(IDX_FK_OTHER_EXPS_V_JOBS))(SALES INDEX (IDX_FK_SALES_V_JOBS))(SALES
INDEX (IDX_FK_SALES_V_JOBS))(DP_EXPS INDEX (IDX_FK_DP_EXPS_V_JOBS))
(DP_EXPS INDEX (IDX_FK_DP_EXPS_V_JOBS))(EXEC_EXPS INDEX
(IDX_FK_EXEC_EXPS_V_JOBS))(EXEC_EXPS INDEX (IDX_FK_EXEC_EXPS_V_JOBS))
(FIELD_EXPS INDEX (IDX_FK_FIELD_EXPS_V_JOBS))(FIELD_EXPS INDEX
(IDX_FK_FIELD_EXPS_V_JOBS))(OTHER_EXPS INDEX
(IDX_FK_OTHER_EXPS_V_JOBS))(OTHER_EXPS INDEX
(IDX_FK_OTHER_EXPS_V_JOBS))(SALES INDEX (IDX_FK_SALES_V_JOBS))(SALES
INDEX (IDX_FK_SALES_V_JOBS))(SALES INDEX (IDX_FK_SALES_V_JOBS))(SALES
INDEX (IDX_FK_SALES_V_JOBS))(CLIENTS INDEX (IDX_PK_CLIENTS))(EXECS
INDEX (IDX_PK_EXECS))(V_EST_V_ACT_COMM_JOBS_PT_2 V J INDEX
(IDX_JH_JOB_COMMISSIONED_STATUS))


THE ERROR MESSAGE - in part is

DELPHI32 caused an invalid page fault in module IB_objects.dpl at
017f:04f250f6 etc