Oracle SQL always shows three rows in a query with joins
I am returning the following data from the database. Based on the state, I calculate the number of items in a particular state and the age of the oldest item.
select distinct 1, dm1.technology, dm1.name_event, count(dm1.id) as "Number of items", max(TO_CHAR(TO_DATE('20000101','yyyymmdd')+(SYSDATE - dm1.time_event),'hh24:mi:ss')) as "Time in system" from dm_procmon dm1 join dm_procmon dm2 on dm1.id = dm2.id
where dm1.name_event = 'state1' and (select count(*) from dm_procmon dm2 where dm1.id= dm2.id and dm2.name_event = 'state2') = 0
group by dm1.technology, dm1.name_event
UNION
select distinct 2, dm1.technology, dm1.name_event, count(dm1.id) as "Number of items", max(TO_CHAR(TO_DATE('20000101','yyyymmdd')+(SYSDATE - dm1.time_event),'hh24:mi:ss')) as "Time in system" from dm_procmon dm1 join dm_procmon dm2 on dm1.id = dm2.id
where dm1.name_event = 'state2' and (select count(*) from dm_procmon dm2 where dm1.id= dm2.id and dm2.name_event = 'state3') = 0
group by dm1.technology, dm1.name_event
UNION
select distinct 3, dm1.technology, dm1.name_event, count(dm1.id) as "Number of items", max(TO_CHAR(TO_DATE('20000101','yyyymmdd')+(SYSDATE - dm1.time_event),'hh24:mi:ss')) as "Time in system" from dm_procmon dm1
where dm1.name_event = 'state3' and (select count(*) from dm_procmon dm2 where dm1.id= dm2.id and dm2.name_event = 'end state') = 0
group by dm1.technology , dm1.name_event
The problem is that when there are 0 items in a certain state, the line of that state is ignored. This means that if, for example, the state is 3 0 items, the row is not displayed at all ...
Current output is incorrect
1 TECHNOLOGY NAME_EVENT Number of items Time in system
---------- -------------------- ----------------- --------------- --------------
1 Repository state1 4 00:19:51
2 Repository state2 1 00:28:21
The result I want to achieve
1 TECHNOLOGY NAME_EVENT Number of items Time in system
---------- -------------------- ----------------- --------------- --------------
1 Repository state1 4 00:19:51
2 Repository state2 1 00:28:21
3 Repository state3 0 00:00:00
+3
source to share
2 answers
Check this query (Oracle 11g required):
with data1 as (
select id, technology tg,
max(decode(name_event, 'state1', 1, 0)) st1,
max(decode(name_event, 'state2', 1, 0)) st2,
max(decode(name_event, 'state3', 1, 0)) st3,
sum(decode(name_event, 'end state', 1, 0)) st4,
max(to_char(date '2000-01-01'+(sysdate - time_event), 'hh24:mi:ss')) tis
from dm_procmon group by id, technology ),
data2 as (
select tg, count(case when st1 = 1 and st2 = 0 then id end) st1,
count(case when st2 = 1 and st3 = 0 then id end) st2,
count(case when st3 = 1 and st4 = 0 then id end) st3,
max(case when st1 = 1 and st2 = 0 then tis else '00:00:00' end) tis1,
max(case when st2 = 1 and st3 = 0 then tis else '00:00:00' end) tis2,
max(case when st3 = 1 and st4 = 0 then tis else '00:00:00' end)tis3
from data1 group by tg
)
select tg technology, st state, max(noi) items, max(tis) max_time
from (
select *
from data2
unpivot (noi for st in (st1 as 'state1', st2 as 'state2', st3 as 'state3'))
unpivot (tis for ti in (tis1 as 'state1', tis2 as 'state2', tis3 as 'state3'))
)
where st=ti group by tg, st order by tg, st
I managed to simplify this query a bit, the last part should be:
select tg technology, event, items, max_time
from data2
unpivot ((items, max_time) for (event, t) in (
(st1, tis1) as ('state1', null),
(st2, tis2) as ('state2', null),
(st3, tis3) as ('state3', null) ))
order by technology, event
+1
source to share
You can explicitly add a string using UNION and DOES NOT EXIST .
For example, consider a test case:
SQL> SELECT deptno dept, count(*) cnt FROM emp WHERE deptno = 20
2 GROUP BY deptno
3 UNION
4 SELECT deptno dept, count(*) cnt FROM emp WHERE deptno = 100
5 GROUP BY deptno
6 /
DEPT CNT
---------- ----------
20 5
SQL>
I want the line to be displayed even if there are no divisions with deptno = 100.
SQL> SELECT to_char(deptno) dept, count(*) cnt FROM emp WHERE deptno = 20
2 group by to_char(deptno)
3 UNION
4 SELECT 'No departments found' dept, 0 cnt
5 FROM dual
6 WHERE NOT EXISTS
7 (SELECT 1 FROM emp WHERE deptno = 100
8 )
9 /
DEPT CNT
---------------------------------------- ----------
20 5
No departments found 0
SQL>
There are no rows in the above example for department = 100, but I am showing it.
+1
source to share