--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@...> wrote:

>
> This query returns 'no current record for fetch operation'.
>
> select
> rg.ID,
> sum(p.ppu * p.to_buy * (case when p.multisrc='Y' then
> rg.buyout_mult_gener else rg.buyout_mult_brand end))
> from
> CRM_RPL_GRP rg
> join P_GET_BUYOUT_ITEMS(current_date,rg.ID,'N') p
> on 1=1
> where
> rg.ID = '01Q'
> group by
> rg.ID

The optimiser does not realise it needs to get CRM_RPL_GRP.ID before
it can get the record from the stored procedure. You will need to use
a left join to force this behaviour, then use the where clause to
emulate an inner join.

This comes up about once a fortnight in the lists.


select
rg.ID,
sum(p.ppu * p.to_buy * (case when p.multisrc='Y' then
rg.buyout_mult_gener else rg.buyout_mult_brand end))
from
CRM_RPL_GRP rg
left join P_GET_BUYOUT_ITEMS(current_date,rg.ID,'N') p
on 1=1
where
rg.ID = '01Q'
and p.ppu is not null
group by
rg.ID

Will work as long as the return parameter P_GET_BUYOUT_ITEMS.ppu is
never null when returned from the procedure.

Adam