All geek questions in one place

Mix Connect, internal join and sum with Oracle

I need help requesting an oracle. A.

Here is my setup:

I have 2 tables, called tasks and schedules, respectively. The "task" table is recursive, so each task can have several subtasks. Each timeline is associated with a task (not necessarily a “root” task) and contains the number of hours worked.

Example:

Tasks

id: 1 | name: Task A | parent_id: NULL

id: 2 | name: Task A1 | parent_id: 1

id: 3 | name: Task A1.1 | parent_id: 2

id: 4 | name: Task B | parent_id: NULL

id: 5 | name: Task B1 | parent_id: 4

Timesheets

id: 1 | task_id: 1 | hours: 1

id: 2 | task_id: 2 | hours: 3

id: 3 | task_id: 3 | hours: 1

id: 5 | task_id: 5 | Opening hours: 1 ...

What I want to do:

I need a query that will return the sum of all hours of work on the "task hierarchy". If we look at the previous example, then I would like to get the following results:

task A - 5 hours | task B - 1 hour (s)

I tried this first

SELECT TaskName, Sum(Hours) "TotalHours" FROM ( SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, ts.hours as hours FROM tasks t INNER JOIN timesheets ts ON t.id=ts.task_id START WITH PARENTOID=-1 CONNECT BY PRIOR t.id = t.parent_id ) GROUP BY TaskName Having Sum(Hours) > 0 ORDER BY TaskName

And it almost works. The only problem is that if there is no schedule for the root task, it will skip the entire hierarchy ... but there may be schedules for child rows, and this is exactly what happens with task B1. I know this is part of the “inner join” that causes my problem, but I'm not sure how I can get rid of it.

Any idea how to solve this problem?

thanks

+4
source share
3 answers

Something like this work? I had cases similar to yours, and I just removed the union from the hierarchical query and applied it only after that, so as not to lose the rows.

 SELECT TaskName, Sum(ts.hours) "TotalHours" FROM ( SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, t.id FROM tasks t START WITH PARENTOID=-1 CONNECT BY PRIOR t.id = t.parent_id ) tasks INNER JOIN timesheets ts ON tasks.id=ts.task_id GROUP BY TaskName Having Sum(ts.hours) > 0 ORDER BY TaskName
+3
source

Have you tried this?

 SELECT TaskName, Sum(Hours) "TotalHours" FROM ( SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, ts.hours as hours FROM timesheets ts LEFT OUTER JOIN tasks t ON t.id=ts.task_id START WITH PARENTOID=-1 CONNECT BY PRIOR t.id = t.parent_id ) GROUP BY TaskName Having Sum(Hours) > 0 ORDER BY TaskName
+1
source

If you use a left outer join instead of a regular join, you can get the result.

 SELECT TaskName, Sum(Hours) "TotalHours" FROM ( SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, ts.hours as hours FROM tasks t,timesheets ts where t.id=ts.task_id(+) START WITH PARENTOID=-1 CONNECT BY PRIOR t.id = t.parent_id ) GROUP BY TaskName Having Sum(Hours) > 0 ORDER BY TaskName
0
source

Source: https://habr.com/ru/post/1311102/

More articles:


All Articles