Problem
Users keep thinking a SQL UDF runs SQL.
They think the SQL UDF works just like the conceptual lookup.
Select Widget_Id, Get_Widget_Brand_Name(Widget_Id) From Widgets
They don’t think to write it as SQL.
Select Widgets.Widget_Id, WidgetBrands.BrandName
From Widgets Inner Join WidgetBrands on Widgets.Brand_Id = WidgetBrands.Brand_Id;
I think we take for granted SQL in this case and we want it in a more usable way. Maybe this is the right direction to go.
Propose
Propose a SubqueryUDF. Essentially you could define the UDF by mapping input parameters to a subquery’s filter/join criteria. The optimizer would unwrap the UDF into the subquery. This functionality would allow a more streamlined way of using SQL. It would reduce total text, increase readability, and appeal to wide variety of users.
Example
The SQL is submitted as such:
Select Widget_Id, Get_Widget_Brand_Name(Widget_Id) From Widgets
The SQL is treated by the optimizer as such:
Select Widgets.Widget_Id, WidgetBrands.BrandName From Widgets Inner Join WidgetBrands on Widgets.Brand_Id = WidgetBrands.Brand_Id;
