Looking for help with SQL - Message "Use of labeled duration not valid."

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

Re: Looking for help with SQL - Message \"Use of labeled duration not valid.\"

Hello Gudrun,

"Hours" is SQL400 reserved word, it is "labeled duration" that's used in date/time arithmetic e.g. NOW() + n HOURS. Just change it something else like TotHours.

Hope this helps,
Bojan

Re: Looking for help with SQL - Message \"Use of labeled duration not valid.\"

I don’t think you need to use the “a.” label in the outer select. Also, you may want to look into Common Table Expressions to replace the nested table expressions.

By the way, nice cat ;-)

Jen

Jen Halverson | S4i Systems | 949.366.5234
Add me to your contacts: Text S4i to 50500
Watch our new video s4isystems.com/info

Re: Looking for help with SQL - Message \"Use of labeled duration not valid.\"

Hello, Bojan and Jen!

Thanks for great + fast help! It were indeed the HOURS in my query, it is running now, I just used quotes. I left the nested table expressions for now, still need to familarize with the common ones, but will definitely look at them as an alternative for future queries. Am not exactly sure about the advantages / disadvantages of both ways for queries like my example, would be great to have some additional info...

Kind regards from Holland,

Gudrun