As part of using WebFOCUS to replace our current reporting solution, we are moving our reports from older SQL Server based stored procedures. In many of our stored procedures, there's a table-valued function that is called for each output row on a report.

The function takes in four values (participant key, organization key, start date and end date) and returns a table with one row and two fields. Those two fields are the participant report ID and participant report ID type.

Because this function may be used in places other than reports, I've been instructed to use the function rather than do a rewrite or duplicating the logic in WF. I really want to avoid having to call the existing stored procedure if possible, because that would lead to a large amount of duplicated metadata throughout the many SPs.

In other cases where I've seen people asking about table-valued functions, they've always talked about having the parameters sent to the function coming from the main amper variables. In this case, each row of output would need to go through the function separately, so it sounds like those types of approaches would not work.

Does anyone have any thoughts on the best way to call this function for each row of output? Or do I need to come up with another solution?

This message has been edited. Last edited by: bsullivan,

Hi bsullivan,

You may be able to use DB_EXPR for this and call the SQL Server function. The DB_EXPR function is new in releases 7.7.05/8.0.01. It allows for the insertion of native SQL expressions 'as-is' into generated native SQL for a FOCUS and SQL expression.

This is discussed further in WebFOCUS New Features 7.7.05 .

Regards,

Kathryn

Kathryn,

Thanks for the reply and I appreciate the help.

From reading the documentation on the sites and trying this out a few ways, I am not sure if I'd be able to use this here (although it may be due to me not understanding what is meant on the documentation).

I don't know if this matters, but the participant key and the organization key are on different tables (in the same database).

I first tried doing a COMPUTE when I was creating a simple report that involved those two fields. However, that returned errors while I was trying to hit "OK" or "APPLY", saying that the formula was not valid. The help site says that DB_EXPR can be used in a COMPUTE if the request is an aggregate request, which may be why I'm unable to do that part.

I then tried writing the data to a HOLD file and doing a DEFINE from there. I was able to get the formula to save that time, but when I tried to report on the new field, I got an error that says "(FOC32605) NON OPTIMIZABLE EXPRESSION WITH SQL SPECIFIC SYNTAX: DB_EXPR".

I tried searching online for that code, but I wasn't able to find anything helpful on what that error meant. When I searched the help site for that error code, it says that a function must be a "row-based scalar function", but I don't know if that specifically refers to using the SQL.Function task.

Do I need to do something to the data set in order to optimize it (and if so, what do I need to do, because a search for 'optimize' isn't turning up too much)? Or is there something else I should be trying?

Hi bsullivan,

Since this looks like it's going to require some research, I'd like to recommend opening a case on InfoResponse Online.

Regards,

Kathryn

Done. Thanks for the help.

I'll be sure to update this thread once I get a solution.

While DB_EXPR works for most SQL constructs in the SELECT list it can't be used to replace a table name.

However there are two alternatives.

Create a file with an expression that references the table function, for example (with just one parameter):

SELECT * FROM TableFunction (42)  

and save it with a file type of SQL. Then create a new synonym and from the "Restrict object type to" pull-down select "External SQL Scripts" and enter the location. You can Sample Data or use the synonym as a source.

The only problem is the parameter values are hard coded.

Another approach is to create an MS SQL Server stored procedure that does nothing more than call your function.

CREATE PROCEDURE TableFun @inp int AS
SELECT * FROM TableFucntion(@inp)

Create a synonym for that selecting "Stored Procedures" from the pull-down. Now when you Sample Data you are prompted for a value for &INP. You can use the synonym as a source and supply value(s) in a WHERE or JOIN.

Clif - I apologize for not replying earlier, but I appreciate the help.

After some internal discussions about where we want to go with WebFOCUS and how the table-valued function is being used in the database, it was finally determined that I could rewrite the logic in WebFOCUS instead of using the existing function.

As a result, I'm marking this as closed. Thank you both for your help.