🚀 go-pugleaf

Hi,

After many years of working with IB i'm still confused with:
How the core engine handles selectable stored procedures in select 
statements.

No problem on this one:

select *
   from sp_months('2006-01-01','today')

--> give a record for every month
1/01/2006	2006	1
1/02/2006	2006	2
1/03/2006	2006	3
1/04/2006	2006	4
1/05/2006	2006	5
1/06/2006	2006	6
1/07/2006	2006	7
1/08/2006	2006	8
1/09/2006	2006	9
1/10/2006	2006	10
1/11/2006	2006	11
1/12/2006	2006	12
1/01/2007	2007	1

But, the selectable stored procedure sp_sb_calc_booked_value calculates 
a specific value that was/is booked for a person for a specific month.

select cbm.*
   from sp_months('2003-01-01','today') m
        inner join sp_sb_calc_booked_value(
                     :pe_idperson,
                     m.out_year,
                     m.out_month) cbm on (1=1)

This should gives us a list of all months, including the booking-info of 
this month.

Now, the rare thing is (all the servers running SS 7.5.1 on Windows) 
that sometimes for no reason such type of queries crashes to server.

So, What can we do with selectable stored procedures:
1) May we use a column of a previous joined relation as INPUT param?
2) Sometimes changing the inner join <selectable stored procedure> to 
outer join <stored procedure> is solving these annoying crashes.

PS
This was a query that was tested in Development, Testing and 
Pre-production on different machines.  But the statement failed NOT the 
first time on the production server but the n-time.  The failing was a 
server crash!

       select xmu.xmu_description
         from sp_mu_controle_modules(
                :pe_idperson,
                :in_old_ro_idregionoffice,
                :in_new_ro_idregionoffice,
                :mu_mutationdate) sp
              left outer join t_codemuerrors xmu on
                    xmu.xm_module = sp.out_xt_module
                and xmu.xmu_codemuerrors = sp.out_errorcode
                and xmu.xl_language = 'NL'
        where xmu.xmu_description is not null

The query was checking if all conditions were right to shift his status 
from an old region to a new region.

Van den Wouwer Danny
Peopleware NV