DB2 Query Structure Using User-Defined Function as a Table

I'm a little new to DB2, and am having trouble developing a query. I have created a user-defined function that returns a table of data which I want to then join and select from in larger select statement. I'm working on a sensitive db, so the query below isn't what I'm literally running, but it's almost exactly like it (without the other 10 joins I have to do lol).

select 
  A.customerId,
  A.firstname,
  A.lastname,
  B.orderId,
  B.orderDate,
  F.currentLocationDate,
  F.currentLocation
from 
  customer A
  INNER JOIN order B
    on A.customerId = B.customerId
  INNER JOIN table(getShippingHistory(B.customerId)) as F
    on B.orderId = F.orderId
where B.orderId = 35

This works great if I run this query without the where clause (or some other where clause that doesn't check for an I开发者_高级运维D). When I include the where clause, I get the following error:

Error during Prepare 58004(-901)[IBM][CLI Driver][DB2/LINUXX8664] SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "Bad Plan; Unresolved QNC found".) SQLSTATE=58004

I have tracked the issue down to fact that I'm using one of join criteria for the parameters (B.customerId). I have validated this fact by replacing B.customerId with a valid customerId, and the query works great. Problem is, I don't know the customerId when calling this query. I know only the orderId (in this example).

Any thoughts on how to restructure this so I can make only 1 call to get all the info? I know the plan is the problem b/c the customerId isn't getting resolved before the function is called.

---

So if I understand correctly, the function getShippingHistory(customerId) returns a table.

And if you call it with a single customer Id that table gets joined in your query above no problem at all.

But the way you have the query written above, you are asking db2 to call the function for every row returned by your query (i.e. every b.customerId that matches your join and where conditions).

So I'm not sure what behaviour you are expecting, because what you're asking for is a table back for every row in your query, and db2 (nor I) can figure out what the result is supposed to look like.

So in terms of restructuring your query, think about how you can change the getShippingHistory logic when multiple customer Ids are involved.

---

i found the best solution (given the current query structure) is to use a LEFT join instead of an INNER join in order force the LEFT part of the join to happen which will resolve the customerId to a value by the time it gets to the function call.

select 
  A.customerId,
  A.firstname,
  A.lastname,
  B.orderId,
  B.orderDate,
  F.currentLocationDate,
  F.currentLocation
from 
  customer A
  INNER JOIN order B
    on A.customerId = B.customerId
  LEFT JOIN table(getShippingHistory(B.customerId)) as F
    on B.orderId = F.orderId
where B.orderId = 35