Formatting data in an IR using SQL

Hi all,

I hope you can help.

I have SQL which is the data source for an IG. The SQL aggregates hours spent working on specific tasks and gather info from other tables in sub-queries. The duration is stored in minutes but I want to display the duration in hh: mm format. I have done this successfully for the primary query, however, the sub-queries fail with "ORA-00921: unexpected end of SQL command". The query is below.

If I remove the trim()... from the sub-query it works fine but the format of the duration is just in minutes rather than hh:mm.

I would appreciate any help you might be able to offer.

This code fails

select c.week_starting,
trim(to_char(trunc(sum(c.DUR_IN_MINS) / 60), '9')) || ':' || trim(to_char(trunc(mod(abs(sum(c.DUR_IN_MINS)), 60)), '09')) AS CLIENT_HHMM,
(
select trim(to_char(trunc(sum(r.DUR_IN_MINS) / 60), '9')) || ':' || trim(to_char(trunc(mod(abs(sum(r.DUR_IN_MINS)), 60)) AS ACT_HHMM
from FPO_CLIENT_REL_ACT r
where c.week_starting = r.week_starting
)
from FPO_SECT_A_PRO_PRAC c
group by c.week_starting

THIS WORKS FINE


select c.week_starting,
trim(to_char(trunc(sum(c.DUR_IN_MINS) / 60), '9')) || ':' || trim(to_char(trunc(mod(abs(sum(c.DUR_IN_MINS)), 60)), '09')) AS CLIENT_HHMM,
(
select r.DUR_IN_MINS AS ACT_HHMM
from FPO_CLIENT_REL_ACT r
where c.week_starting = r.week_starting
)
from FPO_SECT_A_PRO_PRAC c
group by c.week_starting