jrodenhi wrote:

> I am writing a payroll program. I need to display on a check stub the
> current amount and the year to date amount for all of the deductions.
> I would like to do something like
>
> SELECT t.Description, d.CurrentAmount, sp.YTDAmount
> FROM Detail d
> INNER JOIN PayType t ON t.hKey = d.hPayType
> INNER JOIN sp_YTDAmt(d.hCheck, d.hPayType) sp ON 1=1
>
> but I am not sure about what to put in the ON clause of the inner join
> to the stored procedure. I reviewed the section in The Firebird Book
> on selectable stored procedures and I have searched the newsgroup but
> I am not finding anything that touches on this directly.
>
> Is it just that you cannot join to a stored procedure? It appears
> that I can rewrite this using a stored procedure to enclose a cursor
> that first gets the Description and CurrentAmount and then calls
> another stored procedure to get the YTDAmount. Is this the right
> approach?
>
> Thanks for your help.
>
> -Jack Rodenhi
>

Jack,

You could join to SP's the join condition will vary acording to what you
procedure returns.

if your SP returns only a sub-set that is already related to the Detail
table, then you just could use 1=1 as the join condition.

but use left join to force the table to be processed before the SP
otherwise you will get an error, something like this:

SELECT t.Description, d.CurrentAmount, sp.YTDAmount
FROM Detail d
INNER JOIN PayType t ON t.hKey = d.hPayType
LEFT JOIN sp_YTDAmt(d.hCheck, d.hPayType) sp ON 1=1


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br