Subject RE: [IBO] Syntax of JOIN for STORED PROCEDURE
Author Jeroen W. Pluimers (mailings)
> >select TableA.something
> > , ProcedureB.something
> >from TableA
> >join ProcedureB(TableA.Field1, TableA.Field1)

(note the typo: the second TableA.Field1 should have been TableA.Field2)

> > 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).

If you pass the parameters on to the procedure, and the procedure returns
only rows relevant for those particular parameters, then you don't get a
carthesian product.

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

I might add this as a safety measurement, but I don't think it is needed.

> 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).

I need those null values. But since the query is ReadOnly, it should not
impact IBO too much, right?