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
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...
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
Just set your sql type of your database Query Subject to native and it works.
E.g.:
create the following function in Go_sales:
Create function GetOrdersByMethod( @Method int )
RETURNS TABLE
AS
RETURN select * from order_header where order_method_code = @method
create DATABASE QUERY SUBJECT in FM:
Select * from GetOrdersByMethod(1)
set SQL TYPE to NATIVE / Pass-Through... (under options )
Voila.. it wil work..
That's what I was looking for...thanks!
Your welcome... Please close the topic as described in the board rules.