HELP!!Creating UDF in DB2

[shuchis]

Hello,
I want to create a UDF for some common calculation.
In that, i want to create tables (temporary or using table variable), with some records and later on do some calculations on these.
Finally, the UDF is required to return the processed recordset.
Could somebody please help me on this.

I have tried creating Global Temporary tables inside UDF, which is giving an error.
Can someone tell me how to use Table variables in this?

Urgent help needed!!

 

[gregsimpson]

shuchis,

Does the following link help with your temporary tables?

http://www.idug.org/idug/member/journal/summer00/articl11.cfm

Cheers
Greg

 

[shuchis]

Hello Greg,
Thanks alot for responding!!
Actually, i have created temp tables in SQL proc and they work fine
But now i want to create a UDF and then use temp tables.
When i write exactly the same code in UDF (using Create FUnction)
it gives an error at the Decale global temp table statement
Here is the code

CREATE FUNCTION G2REPORT.FXRATE_BASE_REPCUR( prmRepCurrency VARCHAR(40),
prmReportingDate DATE )
RETURNS TABLE (Trad_prod_Oid decimal(18,0),
Trad_prod_Code varchar(40),
Trad_prod_Curr varchar(40),
Rep_Curr varchar(40),
FX_Rate decimal(10,6))
SPECIFIC G2REPORT.FXRATE_BASE_REPCUR
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
F1: BEGIN ATOMIC

--to select all the TRadable Products and their Base Currency OIDs
//ERROR HERE
DECLARE GLOBAL TEMPORARY TABLE session.tmpPrdDet
( product_oid decimal(18,0),
ccy_oid decimal(18,0),
tp_code varchar(40) ,
Type char(1)
) with replace not logged in usertemp01;

insert into session.tmpPrdDet
select
all_products.Product_Oid,
all_products.ccy_oid,
pi.description ,
all_products.Type
from
g2.product_identifier pi,
g2.product_identifier_type pit,
(
select mfp.mutual_fund_product_oid as product_oid, currency_oid as ccy_oid , 'M' as Type
from g2.mutual_fund_product mfp, g2.product_category pc
where pc.product_category_oid = mfp.product_category_oid and pc.code = 'FSC'
union
select smp.simple_product_oid as product_oid, currency_product_oid as ccy_oid , 'S' as Type
from g2.simple_product smp
union
--select cmp.cash_management_product_oid as product_oid, currency_product_oid as ccy_oid
--from g2.cash_management_product cmp
--union all
select sep.stock_exchange_product_oid as product_oid, se.currency_oid as ccy_oid , 'T' as Type
from g2.stock_exchange_product sep, g2.stock_exchange se
where se.stock_exchange_oid = sep.stock_exchange_oid
) all_products
where
pi.product_oid = all_products.product_oid
and pi.product_identifier_type_oid = pit.product_identifier_type_oid
and pit.code = 'PRIMARY'
order by product_oid;

--To select the FX Rate for Reporting Currency passed, with respect to USD
declare global temporary table session.tmpsellCcyDet
( currency_oid decimal(18,0),
Sell_curr_rate decimal(14,9)
)with replace not logged in usertemp01;

insert into session.tmpsellCcyDet
select
ccy.object_id,
frr.rate
from
g2.currency ccy,
g2.fx_curr_pair fcp
,g2.fx_report_rate frr
where
ccy.code = 'GBP' and
ccy.object_id = fcp.sell_report_currency_oid
and fcp.fx_curr_pair_oid = frr.fx_curr_pair_oid
and fcp.buy_base_currency_oid = (select object_id from g2.currency where code = 'USD')
and reporting_date = prmReportingDate

-- To select the FX Rate for Base currency, with respect to USD
declare global temporary table session.tmpBaseCcyDet
( product_oid decimal(18,0),
currency_oid decimal(18,0),
ccy_code varchar(40),
Base_rep_curr_ex_rate decimal(14,9)
);with replace not logged in usertemp01;

insert into session.tmpBaseCcyDet
select
tmpPrdDet.product_oid,
ccy.object_id,
ccy.code
,frr.rate
from
session.tmpPrdDet tmpPrdDet,
g2.currency_product cp,
g2.currency ccy,
g2.fx_curr_pair fcp
,g2.fx_report_rate frr
where
tmpPrdDet.ccy_oid = cp.currency_product_oid
and cp.currency_oid = ccy.object_id
and ccy.object_id = fcp.sell_report_currency_oid
and fcp.fx_curr_pair_oid = frr.fx_curr_pair_oid
and fcp.buy_base_currency_oid = (select object_id from g2.currency where code = 'USD')
and reporting_date = prmReportingDate;

-- To calculate the Final FX rate between Base Currency and Reporting Currency
declare global temporary table session.tmpFX
( product_oid decimal(18,0),
Base_curr_code varchar(40),
rep_curr_ex_rate decimal(14,9)
)with replace not logged in usertemp01;

insert into session.tmpFX
select
tmpbase.product_oid,
tmpbase.ccy_code ,
tmpsell.Sell_curr_rate/tmpBase.Base_rep_curr_ex_rate
from
session.tmpBaseCcyDet tmpbase, session.tmpsellCcyDet tmpsell;

--returning the final resultset
RETURN select
tmpprd.product_oid as Trad_prod_Oid,
tp_code as Trad_prod_Code,
Base_curr_code as Trad_prod_Curr,
prmRepCurrency as Rep_Curr,
rep_curr_ex_rate as FX_Rate
from session.tmpFX tmpfx, session.tmpPrdDet tmpprd
where tmpfx.product_oid = tmpprd.product_oid;
END

Would wait for ur reply
Thanks and Regards,
Shuchi