Re: SQL Views worth it?

On 8/29/2018 12:12 PM, Steve Richter wrote:

On Wed, Aug 29, 2018 at 1:20 PM, Jonathan Ball <jonball52@xxxxxxxxx> wrote:

The function doesn't "do" anything until it is invoked, either. That
doesn't appear to me to be a valid reason to avoid the use of views. What
the original poster and others are trying to achieve is to keep complex
join and selection predicates out of the application code, and to increase
reusability of the SQL code. From that standpoint, a view or a function
does the same thing.

I think they are fundamentally different. Scalar and table functions
accept arguments. A view does not. Nested views must get almost
exponentially complicated.

You don't need the view to accept arguments.  If you create a view that does all the joining and non-parameterized selection, you then specify the variable values you are specifying as input parameters to your function when you query the view:

select available_qty
  from item_bal_avail_qty_vw
 where whse = :whse_host_var
   and itno = :item_host_var


I agree about the potential complexity of nested views.  I looked at a problem query at my last job, and at first glance I thought "What's the problem?  Simple query against one 'table'."  Then I put the thing through Visual Explain, and yow! there must have been at least 40 table references.  That seemingly simple 'table' was in fact a view that referenced other view, which in turn referenced still other views.  It was a complete mess.  I think a lot of queries that got encapsulated into views at that place were written using a query generating tool, and the people who wrote them joined to more tables than they really needed.  Of course, that isn't a blanket indictment of nested views.