guvoko
Member
Looking for help with SQL - Message \"Use of labeled duration not valid.\"
Hello! We switched from Orace Financials to JDE One World XE. Im am a newbie to DB2, trying to "translate" an Oracle query. Am using "iSeries Access for Web" (not sure which version).
I would like to make a select with summarization on two select statement being combined by "union":
select a.employee_number, a.period, sum(a.ot_hours) hours, sum(a.amount), sum(a.meals)
from
(select -- Overtime amount
a.ytdwk period,
a.ytan8 employee_number,
sum(0.01*a.ytphrw) ot_hours,
sum(0.01*a.ytgpa) amount,
0.0 meals
from f0618_inc a
where a.ytpdba in (303,304)
and a.ytdgl =109249
group by a.ytdwk, a.ytan8
union
select -- Overtime meals
b.ytdwk period,
b.ytan8 employee_number,
sum(0.01*b.ytphrw) ot_hours,
0.0 amount,
sum(0.01*b.ytgpa) meals
from f0618_inc b
where b.ytpdba in (334)
and b.ytdgl =109249
group by b.ytdwk, b.ytan8) a
group by a.employee_number, a.period
order by a.employee_number, a.period
This errors: "SQL request failed: [SQL0187] Use of labeled duration not valid."
When I run only the 2 selects with the union as per below it works:
select
a.ytdwk period,
a.ytan8 employee_number,
sum(0.01*a.ytphrw) ot_hours,
sum(0.01*a.ytgpa) amount,
0.0 meals
from f0618_inc a
where a.ytpdba in (303,304)
and a.ytdgl =109249
group by a.ytdwk, a.ytan8
union
select
b.ytdwk period,
b.ytan8 employee_number,
sum(0.01*b.ytphrw) ot_hours,
0.0 amount,
sum(0.01*b.ytgpa) meals
from f0618_inc b
where b.ytpdba in (334)
and b.ytdgl =109249
group by b.ytdwk, b.ytan8
Is there a way to create this "select on select" query without having to create a view?
Thanks in advance and best regards,
Gudrun
Hello! We switched from Orace Financials to JDE One World XE. Im am a newbie to DB2, trying to "translate" an Oracle query. Am using "iSeries Access for Web" (not sure which version).
I would like to make a select with summarization on two select statement being combined by "union":
select a.employee_number, a.period, sum(a.ot_hours) hours, sum(a.amount), sum(a.meals)
from
(select -- Overtime amount
a.ytdwk period,
a.ytan8 employee_number,
sum(0.01*a.ytphrw) ot_hours,
sum(0.01*a.ytgpa) amount,
0.0 meals
from f0618_inc a
where a.ytpdba in (303,304)
and a.ytdgl =109249
group by a.ytdwk, a.ytan8
union
select -- Overtime meals
b.ytdwk period,
b.ytan8 employee_number,
sum(0.01*b.ytphrw) ot_hours,
0.0 amount,
sum(0.01*b.ytgpa) meals
from f0618_inc b
where b.ytpdba in (334)
and b.ytdgl =109249
group by b.ytdwk, b.ytan8) a
group by a.employee_number, a.period
order by a.employee_number, a.period
This errors: "SQL request failed: [SQL0187] Use of labeled duration not valid."
When I run only the 2 selects with the union as per below it works:
select
a.ytdwk period,
a.ytan8 employee_number,
sum(0.01*a.ytphrw) ot_hours,
sum(0.01*a.ytgpa) amount,
0.0 meals
from f0618_inc a
where a.ytpdba in (303,304)
and a.ytdgl =109249
group by a.ytdwk, a.ytan8
union
select
b.ytdwk period,
b.ytan8 employee_number,
sum(0.01*b.ytphrw) ot_hours,
0.0 amount,
sum(0.01*b.ytgpa) meals
from f0618_inc b
where b.ytpdba in (334)
and b.ytdgl =109249
group by b.ytdwk, b.ytan8
Is there a way to create this "select on select" query without having to create a view?
Thanks in advance and best regards,
Gudrun