At 03:18 PM 30-08-01 +0200, you wrote:

>Helen,
>
> > >join VacancyOverView(vacancyturnaroundtimereport.companyid,
> > >vacancyturnaroundtimereport.vacancyid)
> > > on 1=1
> >
> > This isn't valid join syntax.
>
>So how do I join the output of a stored procedure with a regular table or
>view then?
>
>I use this:
>
>select TableA.something
> , ProcedureB.something
>from TableA
>join ProcedureB(TableA.Field1, TableA.Field1)
> on 1=1
>
>The "ON 1=1" will perform the JOIN for ALL records in TableA, which is
>exactly what I want.

Logically it does because 1=1 is always true; but the parser depends on knowing which columns form the join and which ones are the selection criteria. I believe the output from your query will generate a Cartesian product (each output row for set B will be output for each row in set A).

select TableA.something,
ProcedureB.something
from TableA
left join ProcedureB(:param1, :param2)
on TableA.KeyColumn = ProcedureB.KeyColumn

btw, if you are using the same column in TableA to provide values for two different parameters for ProcedureB, you will need to give the parameters different names.

For the view:
select TableA.something,
ViewB.something
from TableA
left join ViewB
on TableA.KeyColumn = ViewB.KeyColumn
where TableA.Field1 = :param1 (or whatever you need).

Why not output the entire dataset from a single procedure instead of relying on the left join, which will give you null values on unmatchable rows? (This is a bit OT for this list but it does impact on performance and complexity on the IBO side).

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________