Tables in our Db2 database are organised by company so as an example ev$paydetail vs he$paydetail hold paydetail for two different companies. I am wondering if it is possible and how to create a user defined table function where I can provide the Company code IE "HE" or "EV" and data from the correct table would be returned.

CREATE or Replace FUNCTION EVANPAYDTA.fncGetPayDetailByCompany(COcode Char(2) ) RETURNS TABLE(GrossPay Decimal(14,3) )

Return ...Sql statements...etc..

I am hoping that i can use PL command logic to select which table will be used as the return data.

We have lots of defined companies and It would be nice, if possible to be able to create complex User defined table functions that will operate for all the companies rather that duplicating the UDTF for each company. We are using db2 v7r2 on a Iseries server

Thanks in advance !