Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle/SQL: Include zero counts when joining non-existent table/data

Posted on 2014-01-22
7
Medium Priority
?
1,447 Views
Last Modified: 2014-01-22
Hi Experts,

I'm trying to construct a query that includes zero counts for values that don't exists in another table:

My Table:
name  |total1|total2|date
george|10      |20     |09-JAN-14
jim      |15      |5       |09-JAN-14
john    |8        |25     |09-JAN-14
george|30      |20     |10-JAN-14
jim      |15      |25     |10-JAN-14
john    |18      |22     |10-JAN-14
george|30      |20     |11-JAN-14
jim      |15      |25     |11-JAN-14
john    |18      |22     |11-JAN-14

Query:
select *
from myTable
where date in ('09-JAN-14','10-JAN-14')
order by name

Results:
name  |total1|total2|date
george|10      |20     |09-JAN-14
george|30      |20     |10-JAN-14
jim      |15      |5       |09-JAN-14
jim      |15      |25     |10-JAN-14
john    |8        |25     |09-JAN-14
john    |18      |22     |10-JAN-14

If I include a date that doesn't exist in the data e.g.:

select *
from myTable
where date in ('15-JAN-14')
order by name

I get no results. That's not what I want. I want to display zero counts e.g.

name  |total1|total2|date
george|0       |0        |15-JAN-14
jim      |0      |0        |15-JAN-14
john    |0      |0        |15-JAN-14

I'll appreciate any help.

Thanks in advance...
0
Comment
Question by:rmartes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39799976
That's a more complicated  query than you might think.

Try this...


  SELECT n.name,
         NVL(t.total1, 0) total1,
         NVL(t.total2, 0) total2,
         d.date
    FROM mytable t
         RIGHT JOIN (SELECT DISTINCT name FROM mytable) n ON t.name = n.name
         RIGHT JOIN (SELECT '09-JAN-14' date FROM DUAL
                     UNION ALL
                     SELECT '10-JAN-14' FROM DUAL) d
             ON t.date = d.date
ORDER BY name
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39799995
hmmm, no that doesn't quite work for the missing data either but I think I misunderstood the request anyway - stay tuned, I'll try again.  :)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39800016
what if you had this...

where date in ('15-JAN-14', '16-JAN-14')


would you want 6 rows of 0 values?

or what about '10-JAN-14, '15-JAN-14'

do you want both the populated and unpopulated values?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:rmartes
ID: 39800311
@sdstuber

Exactly....

where date in ('15-JAN-14', '16-JAN-14') should show 6 rows of zero values

and date in ('10-JAN-14, '15-JAN-14') should be combination of both
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39800345
try this...  you'll have to put your list of dates into a subquery so it can be used as a join, not just as an IN-List

With d
     AS (SELECT '09-JAN-14' mydate FROM DUAL
         UNION ALL
         SELECT '10-JAN-14' FROM DUAL
        )
SELECT t.*
  FROM mytable t, d
 WHERE t.mydate = d.mydate
UNION ALL
SELECT DISTINCT name,
                0,
                0,
                mydate
  FROM (SELECT DISTINCT name FROM mytable) t CROSS JOIN d
 WHERE (t.name, d.mydate) NOT IN (SELECT name, mydate FROM mytable)
ORDER BY 1;

Open in new window



or for not existing data...


With d
     AS (SELECT '15-JAN-14' mydate FROM DUAL
         UNION ALL
         SELECT '16-JAN-14' FROM DUAL)
SELECT t.*
  FROM mytable t, d
 WHERE t.mydate = d.mydate
UNION ALL
SELECT DISTINCT name,
                0,
                0,
                mydate
  FROM (SELECT DISTINCT name FROM mytable) t CROSS JOIN d
 WHERE (t.name, d.mydate) NOT IN (SELECT name, mydate FROM mytable)
ORDER BY 1;

Open in new window



or for a mix



With d
     AS (SELECT '09-JAN-14' mydate FROM DUAL
         UNION ALL
         SELECT '10-JAN-14' FROM DUAL
         UNION ALL
         SELECT '15-JAN-14' FROM DUAL
         UNION ALL
         SELECT '16-JAN-14' FROM DUAL)
SELECT t.*
  FROM mytable t, d
 WHERE t.mydate = d.mydate
UNION ALL
SELECT DISTINCT name,
                0,
                0,
                mydate
  FROM (SELECT DISTINCT name FROM mytable) t CROSS JOIN d
 WHERE (t.name, d.mydate) NOT IN (SELECT name, mydate FROM mytable)
ORDER BY 1;

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39800355
As a simpler syntax for your parameters, try something like this for the "d" portion.
The rest of the query would remain the same


With d
     AS (SELECT COLUMN_VALUE mydate
           FROM TABLE(
                    sys.ora_mining_varchar2_nt(
                        '09-JAN-14',
                        '10-JAN-14',
                        '15-JAN-14',
                        '16-JAN-14'
                    )
                ))

of course, that returns the values a strings, if you want dates
you might want to do something like this...


with d
     AS (SELECT to_date(COLUMN_VALUE,'dd-MON-RR') mydate
           FROM TABLE(
                    sys.ora_mining_varchar2_nt(
                        '09-JAN-14',
                        '10-JAN-14',
                        '15-JAN-14',
                        '16-JAN-14'
                    )
                ))
0
 

Author Closing Comment

by:rmartes
ID: 39801374
Funny thing, I was trying to do it with a WITH clause myself, but couldn't quite get it. Excellent! Thank you so much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question