[Solved] User defined function (UDF) created in SQL Server that returns a table

[st_reed]

does anyone know how I can you a UDF that is created in a sql server database that returns a table datatype?

I have tried using sql (e.g. select * from fx (param) ), but I get an error.


Thanks
S

[sir_jeroen]

What are you looking for? Do you possibly mean a stored procedure. But in any case: Framework manager is the place to look for it...

[st_reed]

Everything in green is the DDL for the UDF in the database (see below).Ã,  An example of how I might use a function like this is to join another table to it, in order to restrict the result set (see text below in blue)

I have tried to do this a couple of different ways in Framework Mgr, but I get an error.Ã,  I have tried a) using a sql statement like the one below b) creating a Query Subject based on a function (or just using a scalar udf function) via the "New Query Subject Wizard".

Method a) yields this error:

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 1: Syntax error near "end-of-statement".


With method b), I can see the UDF in the database, but I cannot add them to the Query Subject.

In searching for an answer, I found numerous mentions to using UDFs in Impromptu, in which you need to declare the function in any .ini file; however, I do not know if this applies to FM.

Additionally, I have found some config files in \\install path\cognos\crn\configuration\functions that appear to define funcitons for various DBMSs; however, I have not had any success trying to modify these so that I can use a function.

Thanks,
S


I have a physical table called tableA, with a column called intFieldA.Ã,  I might issue a command like:

Ã,  Ã, select a.* from tableA a inner join cafe.fn_split ('1,2,3', ',') b on a.intFieldA = b.intvalue

The resulting data will have all of the rows from tableA in which intFieldA is equal to 1 or 2 or 3.



/* returns a table with one row for each @seperator seperated value in @stringOfNumbers */

create function cafe.fn_split (
Ã,  Ã, @stringOfNumbers varchar (7999), @seperator char (1)
)
returns
@tableOfIntValues table (intvalue int) /* this is a table variable (temp table) */
as
begin
/* ...code to split @stringOfNumbers based on @seperator,
then insert individual values into @tableOfIntValues */
   return
end

[sir_jeroen]

Just set your sql type of your database Query Subject to native and it works.

E.g.:
create the following function in Go_sales:

Code Select Expand


Create function GetOrdersByMethod( @Method int )
RETURNS TABLE
AS
RETURN select * from order_header where order_method_code = @method

create DATABASE QUERY SUBJECT in FM:

Code Select Expand

Select   * from  GetOrdersByMethod(1)

set SQL TYPE to NATIVE / Pass-Through... (under options )
Voila.. it wil work..

[st_reed]

That's what I was looking for...thanks!

[sir_jeroen]

Your welcome... Please close the topic as described in the board rules.