Re: iNext Revealed As IBM i 7.3

Hi Mike,

Would putting the table function alone in a CTE, then joining the result of the CTE with a table perform any better? SQL might be able to join that result better.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 2/24/2016 10:32 AM, Mike Jones wrote:

Hi Steve,

You can join to table functions now. I do a lot with table functions.
That said, it can be challenging to get good performance with table
functions and joins. Their results are a black box that return some data,
and it is therefore difficult for the SQL optimizer to predict that
returned set and optimize it with joined data. As a result, I use them
more for performing actions against the database in the middle of set
processing. I also use a table function to call CL programs in the middle
of set processing. You can get good performance with fetching data via
table functions and joins, but it tends to take more engineering effort to
make that happen versus a query with joins that does not involve table
functions.

Mike

Would be neat to have an SQL function return a result set. That way you

could join to the SQL function.
select a.custName, b.ordnum, b.orderDate
from custMaster a
join GetCustomerOrders( a.custNum ) b