1. Robert Sawyer
  2. PowerBuilder
  3. Thursday, 28 March 2024 14:29 PM UTC

How do I use a table-valued SQL function in PB? If I call it in a script I get an error message.  I then tried to create a datawindow using the function as it's data source and PB brought back a message stating I couldn't do this.

John Fauss Accepted Answer Pending Moderation
  1. Thursday, 28 March 2024 15:17 PM UTC
  2. PowerBuilder
  3. # 1

Hi, Robert -

What database (vendor, version, etc.)?

What version/release/build of PB?

Comment
There are no comments made yet.
Robert Sawyer Accepted Answer Pending Moderation
  1. Thursday, 28 March 2024 15:24 PM UTC
  2. PowerBuilder
  3. # 2

MSSQL Server

PB 17

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 28 March 2024 15:54 PM UTC
  2. PowerBuilder
  3. # 3

What database interface?

you cannot call it from PowerScript (a la "Select * from dbo.MyTableFunction(42,'bananas') using SQLCA;") because it returns a table, not a single, bind-able value, but you can include it in a Select statement where the table returned by the SQL function is joined to other table(s) in order to produce a single value.

With the SNC database interface, you can use a table function as the From clause in a Select statement that is the data source for a DataWindow or DataStore, such as "Select * from dbo.RangeOfNumbers(:first_value,:last_value)", where "first_value" and "last_value" have been defined as retrieval arguments.

I know this works with the SNC interface. I've never used ODBC, however.

Comment
There are no comments made yet.
Nasir Ahmad Accepted Answer Pending Moderation
  1. Monday, 1 April 2024 15:00 PM UTC
  2. PowerBuilder
  3. # 4

Try with a stored procedure. I had used stored procedure of MSSQL with PB12.5 years back and it used to work so maybe you can move your logic from DB function to DB stored procedure and give it a try. 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.