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
