Hi.



I am new to stored procedures. I am selecting data for various tables &
placing them in global temporary tables. I have a couple of temp tables
that are each populated by its own insert statement. I then do a select
from all these temp tables to get the data that I want to use in my
class. I need to move these statements into a stored procedure but I
just can't seem to get it to work. As I said, this is the first time I
am working with stored procedures so any help will be appreciated.



I have attached a copy of the statements I am trying to put into the
stored procedure.



Thanks



Marc Mostert

BIS - Development

998-2101

0823750832

[Email address protected] <mailto:[Email address protected]










To read FirstRand Bank's Disclaimer for this email click on the following address or copy into your Internet browser:
https://www.fnb.co.za/disclaimer.html

If you are unable to access the Disclaimer, send a blank e-mail to
[Email address protected] and we will send you a copy of the Disclaimer.
------1. Create table containing all products for the current financial period
declare global temporary table Products (obj_product_group_id int, description varchar(800), ProductWeighting float, Comment varchar(255), Sort_order int) on commit preserve rows not logged;

insert into session.Products (obj_product_group_id, description, ProductWeighting, Comment, Sort_Order)
select pg.id as obj_product_group_id
, pg.description
, pw.value as ProductWeighting
, case when coalesce(rtrim(pg.comment), '') = '' then 'No comment set on this product grouping' else pg.comment end as comment
, pg.sort_order
from
db2inst1.obj_product_group pg
inner join
db2inst1.obj_tss_product_weighting pw
on pg.id = pw.obj_product_group_id
where
pg.target_ind = 1
and pg.financial_period = 2008
with ur;

--------2. Create table containing value and internalvalue (internalvalue to be used in the event that the org unit selected is a branch)
declare global temporary table FixedOther (obj_product_group_id int, Type_Org_Unit_ID int, TargetValue float, Internalvalue float, TotalValue float, ParentTotalValue float) on commit preserve rows not logged;

insert into session.FixedOther (obj_product_group_id, type_org_unit_id, targetvalue, internalvalue, totalvalue, parenttotalvalue)
Select
ft.obj_product_group_id
, ou.type_org_unit_id
, coalesce(ft.value, 0) TargetValue
, coalesce(ft.internalvalue, 0) Internalvalue
, case when ou.type_org_unit_id = 8 then coalesce(ft.internalvalue, 0) else coalesce(ft.value, 0) end TotalValue
, case when ou.type_org_unit_id = 14 then coalesce(ft.value, 0) else coalesce(ft1.value, 0) end ParentTotalValue
from
db2inst1.obj_fixed_target ft
left join
db2inst1.obj_org_unit ou
on ft.obj_org_unit_id = ou.id
left join
db2inst1.obj_fixed_target ft1
on ou.obj_org_unit_parent_id = ft1.obj_org_unit_id
and ft.financial_period = ft1.financial_period
and ft.obj_product_group_id = ft1.obj_product_group_id
where
ft.financial_period = 2008
and ft.obj_org_unit_id = 728
with ur;

--------3. Create a table containing the fixed target totals
declare global temporary table FixedTargets (obj_product_group_id int, TotalValue Float, ParentTotalValue Float, NewFactor Float) on commit preserve rows not logged;

insert into session.FixedTargets (obj_product_group_id, TotalValue, ParentTotalValue, NewFactor)
Select
pg.id
, coalesce(fo.totalvalue, 0) TotalValue
,fo.parenttotalvalue parenttotalvalue
, case
when coalesce(fo.parenttotalvalue, 0) = 0 or coalesce(fo.totalvalue, 0) = 0 then 0
when fo.type_org_unit_id = 8 then coalesce(fo.internalvalue, 0)/coalesce(fo.targetvalue, 0) * 100
else coalesce(fo.totalvalue, 0)/coalesce(fo.parenttotalvalue, 0) * 100
end NewFactor
from
db2inst1.obj_product_group pg
left join
session.FixedOther fo
on pg.id = fo.obj_product_group_id
where
pg.target_ind = 1
and pg.financial_period = 2008
with ur;

--------4. Create a table containing the individual target totals
declare global temporary table CapturedTargets(obj_product_group_id int, CapturedTotal float) on commit preserve rows not logged;

insert into session.CapturedTargets
with parents
(
id, obj_org_unit_parent_id, type_org_unit_id
) as
(
select id, obj_org_unit_parent_id, type_org_unit_id from db2inst1.obj_org_unit where id = 728 and state_org_unit_id = 1
union all
select child.id, child.obj_org_unit_parent_id, child.type_org_unit_id from parents parent, obj_org_unit child where child.obj_org_unit_parent_id = parent.id and state_org_unit_id = 1
)
select
oi.obj_product_group_id
, sum(coalesce(om.value, 0)) CapturedTotal
from
parents pa
inner join
db2inst1.obj_individual_target oi
on pa.id = oi.obj_org_unit_id
inner join
db2inst1.obj_monthly_target om
on oi.id = om.obj_individual_target_id
where
pa.type_org_unit_id in (8, 19)
and oi.financial_period = 2008
and om.value <> 0
group by
oi.obj_product_group_id
with ur;

----------------------------------------------------------------------------
---------------Actual Select Statement Used by Application--------------
----------------------------------------------------------------------------
Select
pd.obj_product_group_id Id
, pd.description Description
, pd.ProductWeighting
, pd.comment
, bigint(round(coalesce(ft.TotalValue, 0),0)) Finaltarget
, decimal(coalesce(ft.NewFactor, 0), 15, 3) Newfactor
, decimal(decimal(round(coalesce(ft.TotalValue, 0),0),15,3) * decimal(pd.ProductWeighting, 15, 3), 15, 3) Targetvalue
, bigint(round(coalesce(ct.CapturedTotal, 0),0)) Capturedtarget
, bigint(round(coalesce(ft.TotalValue, 0),0)) - bigint(round(coalesce(ct.CapturedTotal, 0),0)) Unallocated
from
session.products pd
left join
session.fixedtargets ft
on pd.obj_product_group_id = ft.obj_product_group_id
left join
session.CapturedTargets ct
on pd.obj_product_group_id = ct.obj_product_group_id
order by
pd.sort_order asc
with ur;


DB2 & UDB email list listserv db2-l LazyDBA home page