|Subject||RE: [IBO] Syntax of JOIN for STORED PROCEDURE|
|Author||Jeroen W. Pluimers (mailings)|
> >select TableA.something(note the typo: the second TableA.Field1 should have been TableA.Field2)
> > , ProcedureB.something
> >from TableA
> >join ProcedureB(TableA.Field1, TableA.Field1)
> > on 1=1If you pass the parameters on to the procedure, and the procedure returns
> >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).
only rows relevant for those particular parameters, then you don't get a
> select TableA.something,I might add this as a safety measurement, but I don't think it is needed.
> from TableA
> left join ProcedureB(:param1, :param2)
> on TableA.KeyColumn = ProcedureB.KeyColumn
> Why not output the entire dataset from a single procedure insteadI need those null values. But since the query is ReadOnly, it should not
> 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).
impact IBO too much, right?