Have following proc:
create procedure get_labdet
(in @jobnumber char(12),
@costcode char(8),
@jdedatefrom int,
@jdedateto int)
result set 1
language sql
reads sql data
set option datfmt = *iso
begin
declare c1 scroll cursor with return for
select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,
ytsub as costcode,
b.fapid as equipmentid,
substr(b.fadl01, 1, 20) as equipmentname,
ytpalf as employeename,
sum(decimal(ytphrw * .01, 31, 2)) as employeehours,
sum(decimal(ytexr * .01, 31, 2)) as equipmenthours
from vgiprdhrp/f0618lg a
left outer join vgiprddta/f1201la b
on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb
where ytmcu = @jobnumber and
ytsub = @costcode and
(ytobj = '70100 ' or ytobj = '70105 ' or ytobj = '70110 ' or
ytobj = '70130 ' or ytobj = '60100 ' or ytobj = '60115 ' or
ytobj = '60120 ' or ytobj = '60125 ' or ytobj = '60130 ')
group by ytdwk, ytsub, yteqcg, ytpalf, b.fapid, b.fadl01;
open c1;
end;
Reading records from the F0618LG file, field "yteqcg" may or may not be empty (null). So I need to condition the "left outer join" only to happen if "yteqcg" is not null. Currently I get a mapping error. Is that possible? Thank you.
create procedure get_labdet
(in @jobnumber char(12),
@costcode char(8),
@jdedatefrom int,
@jdedateto int)
result set 1
language sql
reads sql data
set option datfmt = *iso
begin
declare c1 scroll cursor with return for
select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,
ytsub as costcode,
b.fapid as equipmentid,
substr(b.fadl01, 1, 20) as equipmentname,
ytpalf as employeename,
sum(decimal(ytphrw * .01, 31, 2)) as employeehours,
sum(decimal(ytexr * .01, 31, 2)) as equipmenthours
from vgiprdhrp/f0618lg a
left outer join vgiprddta/f1201la b
on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb
where ytmcu = @jobnumber and
ytsub = @costcode and
(ytobj = '70100 ' or ytobj = '70105 ' or ytobj = '70110 ' or
ytobj = '70130 ' or ytobj = '60100 ' or ytobj = '60115 ' or
ytobj = '60120 ' or ytobj = '60125 ' or ytobj = '60130 ')
group by ytdwk, ytsub, yteqcg, ytpalf, b.fapid, b.fadl01;
open c1;
end;
Reading records from the F0618LG file, field "yteqcg" may or may not be empty (null). So I need to condition the "left outer join" only to happen if "yteqcg" is not null. Currently I get a mapping error. Is that possible? Thank you.