Calling DB2 Table Valued Function from Excel

Question

I have created a User Defined Table Valued Function in DB2 which takes in two parameters and returns a Table.

Calling the UDTVF from IBM i Access Client Solutions (Version: 1.1.7.1 Build: 7290) >> Run SQL Scripts works fine returning a table of results:

-- SELECT *
-- FROM TABLE(<library>.<udtvf>(<param. 1>,<Param. 2>)) AS ResultsTable

SELECT *
FROM TABLE(DPLIB06.FISMOSLSWAR(1,2017)) AS ResultsTable

I have an ODBC DSN defined that points to DB2. This DSN works fine as a Data Connection within MS Excel, Crystal Reports, etc. etc. and has, up to this point, worked fine even with some very complex queries.

When I attempt to add the above query to an Excel Sheet via the MS Query Editor I get the following warning/error messages:

SQL Query can't be represented graphically. Continue anyway?

No big deal with this warning: I always click OK and life goes on... but then I get

Could not add the table 'TABLE(DPLIB06.FISMOSLSWAR(1'.

So... Can someone give me the syntax for calling a DB2 User Defined Table Valued Function (with two parameters) from Microsoft Excel?

Try working around the problem. For example, try creating a VIEW in the database to represent the query on the table function. Then in Excel, select from that view. — mao
– mao, Commented Nov 28, 2018 at 15:12
Mao - The reason for using a UDTVF is because a VIEW does not accept Parameters — Swamp_Thing
– Swamp_Thing, Commented Nov 28, 2018 at 16:12

Swamp_Thing · Accepted Answer · 2018-11-28 16:11:02Z

1

Here's a solution that works:

Using the MS Query Editor enter a simple query:

SELECT 'A' FROM SYSIBM.SYSDUMMY1

Close the Editor returning results to your Excel

From the Excel Data >> Connections menu select your Connection and expose its Properties. In the Definition replace the Command text with the query that references the UDTVF

answered Nov 28, 2018 at 16:11

Swamp_Thing

113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calling DB2 Table Valued Function from Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related