AnsweredAssumed Answered

Table Functions - NSQL on steroids

Question asked by Paul_Maxwell Employee on Sep 3, 2009
Latest reply on Dec 7, 2012 by another_martink
Hi. I'm raising this thread due to 2 old forum references:one by Dave [Oracle] & in reply by waely [mssql], both can be found here
http://caforums.ca.com/ca/board/message?board.id=CAClarityGeneralDiscussion&thread.id=2243&view=by_date_ascending&page=2

That topic is summarization of data for graphs and the use of a "table function" is suggested by Dave. I agree! [nb:
in Oracle these might be also known as "pipelined table functions"
in MSSQL these might also be known as "inline table functions" or "inline table UDFs"
]
A "table function" is like any other function; they return something: but what these types of function return "looks like" a table of data. In other words you can access full procedural code capability inside the function AND have it supply back an array of data which can then be used in a SQL select statement. Very cool. As an extra bonus they are usually fast.

The other REALLY GREAT thing about table functions is you can pass parameters into them (e.g. the project id) so can they respond "within context" of where the user is!

I'm not going to detail how to develop a table function in this post, you can research that elsewhere, but I did want to add here that Clarity NSQL queries CAN use them; in both database types. There are differences in NSQL however, for each database type, that are absolutely worth mentioning:
--Under Oracle, you can call a table function by "strongly typed" parameters in the following fashion via NSQL:

from

/* nb: table((...)) requires STRONGLY typed parameters - hence the 2 inner sub-queries are needed */

TABLE(GSE_FF_CONTROL_GAUGE_PKG.GSE_FF_CONTROL_GAUGE_FCT



(



(select distinct id from cmn_sec_users u where id = @WHERE:PARAM:USER_ID@)



,(select language_code from cmn_languages where language_code = @WHERE:PARAM:LANGUAGE@)



)


)

) fuel_function


--Under MSSQL, the equivalent NSQL to call the equivalent table function would look like:

FROM @DBUSER@.GSE_FF_CONTROL_GAUGE_FCT(





@WHERE:PARAM:USER_ID@





,@WHERE:PARAM:LANGUAGE@





)

so:
in Oracle, the syntax is typically:

select * from TABLE(package.function(sub-select1,sub-select-n)) alias -- the sub-selects can reference @WHERE:..param variables

in MSSQL, the syntax is typically:

select * from @dbuser@.function(@WHERE:..param1,@WHERE:..param2) [alias] -- no sub-selects here

If there is sufficient interest in this topic I may be able to post an interesting table function (mssql) you can use. There is a suggested "quid pro quo" (the catch): ask your local CA contacts about the "Fiscal Focus add-on" the code snippets above come from that source. [sorry, advert warning]

have fun.

Outcomes