create procedure SEL_TR120T_201
(
krno varchar(10)
)
returns
(
CODE varchar(4),
TNO varchar(2),
HMNM varchar(20),
GBDT varchar(10),
NEYG varchar(40),
DNKA numeric,
SRNG numeric,
AMNT numeric,
SUYL decimal(3,2)
)
as
begin
/* Procedure Text */
for
select
cast(a.gb_hmcd as varchar(4)) CODE,
cast(a.gb_hmno as varchar(2)) TNO,
cast(b.cd_des2 as varchar(20)) HMNM,
cast(a.gb_date as varchar(10)) GBDT,
cast(a.gb_desc as varchar(40)) NEYG,
cast(a.gb_dnka as numeric) DNKA,
cast(a.gb_srng as numeric) SRNG,
cast(a.gb_amnt as numeric) AMNT,
cast(a.gb_suyl as decimal(3,2)) SUYL
from tr120t a
left outer join tr010t b on b.cd_iden = '3' and a.gb_hmcd
= b.cd_code
where a.gb_krno = :krno
union all
select
cast('' as varchar(4)) CODE,
cast('' as varchar(2)) TNO,
cast('' as varchar(20)) HMNM,
cast('' as varchar(10)) GBDT,
cast('[합 계]' as varchar(40)) NEYG,
cast(0 as numeric) DNKA,
cast(0 as numeric) SRNG,
cast(sum(gb_amnt) as numeric) AMNT,
cast(0 as decimal(3,2)) SUYL
from tr120t
where gb_krno = :krno
into :CODE, :TNO, :HMNM, :GBDT, :NEYG, :DNKA, :SRNG,
:AMNT,:SUYL
do
suspend;
end
위 문장으로 각 항목의 값들을 구한 후 전체 계를 내는 프로시져를
작성 했습니다.
이상한점??
for ~ do 문장은 루프를 돌린다는데요...
저위 union all 다음은 합계만 내면 되는 문장 이거든요... 굳이 루프를
돌리지 않아도 될 텐데요...
방법을 모르겠습니다.
고수님들 맞게 된 문장 인가요?
|