Tuesday, August 18, 2009

view links internal to sql statement

There is an esoteric aspect of links that I had not really experimented with until yesterday.

First know that it is possible to write a query/view object, that contains a bind variable in the query which is not defined in the list of bind variables in the view object itself.

Why would you want to do this?

What if you wanted (for speed reasons) to put a bind variable referencing a parent query column in a subquery of a query in the child view object?

Well, if you create a view link between the parent and child queries, ADF will automatically create a bind variable on behalf of the view-link with the name "Bind_. So if the parent was DeptView, and the child EmpView, and their connecting view link was DeptView.DeptNo = EmpView.DeptNo, then at run time this view link would create :Bind_DeptNo referenceable in the child query. So you could put this in a sub-query.

At the moment this still leaves the sometimes problem that you may have to have a connecting DeptNo in the child query select clause, but that is only a problem in some cases. This bit of flexibility can be good enough in many cases.

Also this may be a case where the problem is that I am not sure just how much more flexibility ADF/BC will allow. It is quite possible that you could connect these two queries based on bogus columns like select 1 bogus_columnn, ... from ... in both the parent and child, and then change the where clause in the view link to join on :Bind_DeptNo. Maybe it is even more flexible than that; I just am not sure at this time.

No comments: