Hi all,
I am a hobbyist writing an APEX app for my own personal use. I am also a SQL newbie so I hope my question makes sense.
I have 4 tables identified in the SQL below. Each of the tables has a few elements in common being week_starting, dur_in_mins, and user.
The issue I have is that if one of the tables does not have a row for the week then the query returns "no data". I have tried using a with clause and also left outer joins. The with option has the same result as the query below and the left outer join duplicates for each row.
I have played around with this for many hours over a few weeks but am unable to solve the problem.
Any idea would be greatly appreciated.
Thanks.
select c.week_starting,
sum(c.DUR_IN_MINS) / 60 AS CLIENT_CONTACT,
(
select sum(r.DUR_IN_MINS) / 60 AS ACT_HHMM
from FPO_CLIENT_REL_ACT r
where r.FK_FPO_USER_ID = :SESSION_USERID
) CLIENT_RELATED,
(
select sum(p.duration_in_mins) / 60 as PD_HHMM
from FPO_SECT_B_PD p
where p.FK_FPO_USERS = :SESSION_USERID
) PROFESSIONAL_DEVELOPMENT,
(
select sum(duration) / 60 as IND_SUP
from FPO_SECT_C_SUPERVISION s
where s.supervisory_type = 'INDIVIDUAL'
and s.FK_FPO_USERS = :SESSION_USERID
) INDIVIDUAL_SUPERVISION,
(
select sum(duration) / 60 as GRP_SUP
from FPO_SECT_C_SUPERVISION s
where s.supervisory_type = 'GROUP'
and s.FK_FPO_USERS = :SESSION_USERID
) GROUP_SUPERVISION,
(
select sum(duration) / 60 as OTHER_SUP
from FPO_SECT_C_SUPERVISION s
where s.supervisory_type = 'OTHER'
and s.FK_FPO_USERS = :SESSION_USERID
) OTHER_SUPERVISION,
(
select sum(c.DUR_IN_MINS) / 60 + sum(r.DUR_IN_MINS) / 60
from FPO_SECT_A_PRO_PRAC c, FPO_CLIENT_REL_ACT r
where c.FK_FPO_USER_ID = :SESSION_USERID
and c.FK_FPO_USER_ID = r.FK_FPO_USER_ID
and c.week_starting = r.week_starting
) WEEK_CL_TOTAL
from FPO_SECT_A_PRO_PRAC c
where FK_FPO_USER_ID = :SESSION_USERID
group by c.week_starting