Oracle SQL: Rows don't display when there are no items

Question

I write a script for an Oracle Database, which logs events in several technologies. An event is normally logged twice, one time when it comes into the technology and one time when it goes out the technology.

What I want to do is to show every technology in my database. From each technology I want to show how many items there are at the moment and what the age of the oldest item is.

A graphical example of how my table should look like: Snippet of how the table should look like

I use following code to calculate these things

select dm1.technology,
       count(dm1.id) as "current_number_of_items",
       TO_CHAR(MAX(TO_DATE('20000101','yyyymmdd')+(SYSDATE - dm1.time_event)),
       'hh24:mi:ss') as "age_of_oldest_item"
from dm_procmon dm1
where (select count(dm2.id) from dm_procmon dm2 where dm1.id= dm2.id) = 1
group by dm1.technology;

For calculating the current number of items I just count the id's and group them by the technology. For calculating the age of the oldest item, I check if an id only appears 1 time in the database (otherwise it's already out). Than I just take the oldest age (SYS - time event from log).

This works perfect but not when there are 0 items in the technology. I want still to show the technology name if there are no items in it. How can I do that?

When you mean 0 items in the technology, will the id and other fields remain empty? Or is there another table holding the technology information ? — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Mar 31, 2015 at 9:14
No it's the same table. I mean when my calculation of current items in the technology gives the number 0, the row is not shown. But I want my technology to be shown in the result. — Jonas Audenaert
– Jonas Audenaert, Commented Mar 31, 2015 at 9:18

Ponder Stibbons · Accepted Answer · 2015-03-31 10:14:21Z

1

I understood you this way: if item same ID appeared in table twice (or more) then it should not be analyzed, but you want this technology listed even if not exists items which appeared only once. This query should do this:

with items as (
  select id, technology, count(1) cnt, max(time_event) tev  
    from dm_procmon group by id, technology )
select technology, sum(decode(cnt, 1, 1, 0)) as "current_number_of_items",
    TO_CHAR(MAX(TO_DATE('20000101','yyyymmdd')
      +(SYSDATE - decode(cnt, 1, tev))), 'hh24:mi:ss') as "age_of_oldest_item"
  from items group by technology order by technology

Subquery items counts appearances for each item. Main query lists all technologies and adds informations for items with count=1 (function decode() does this).

Example results:

TECHNOLOGY             current_number_of_items age_of_oldest_item
---------------------- ----------------------- ------------------
Folder Belfius                               1 00:36:35
Folder KBC                                   0 
Queue KBC                                    1 00:36:35
Repository Accepted                          1 00:36:35
Repository Enriched                          1 00:36:35
Repository Sent                              2 00:36:35

answered Mar 31, 2015 at 10:14

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonas Audenaert Over a year ago

As you said, this works. You perfectly understood what I wanted to achieve. Thanks!

Collectives™ on Stack Overflow

Oracle SQL: Rows don't display when there are no items

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related