Hi Andrew
I'm assuming you're unable to create the procedure, but please be more
specific about your platform and error message(s).
I doubt that query 2 or 3 work outside the stored procedure, as the correct
syntax is LEFT OUTER JOIN, not 'LEFT JOIN'.
Regards
Alex Levy
-----Original Message-----
From: Mostert Andrew Marc
[mailto:db2udbdba-ezmlmshield-x75889728.[Email address protected]
Sent: 14 March 2008 12:34
To: LazyDBA Discussion
Subject: Stored Procedures & Global Temporary Tables
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;
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page