SQL

Hiravaxis

Heya.. I don't know if we've got an SQL or DB thread, but maybe we can start one.

Here, I'll go first.

select 
c.CLA_NAME "Classification",
count (s.ser_cla_oid) "Number"
from
servicecalls s,
classification c
where
s.SER_CLA_OID (+) = c.CLA_OID
group by
c.CLA_NAME
;

So I'm counting the number of times that a particular classification appears in the service calls table, outer joined with the classification table so I pick up any 0 rows.

IE
E-mail 48
Phone 59
Distribution List and Mailbox 298
Network 265
Remove Access 246
Modify Access 217
Printer 211
Add Retailer 0
Maintenance Contract 0

But as soon as I try to add another clause to select only service calls that fall within a particular date range,
I no longer get any zero rows.

This is the extra clause I add:

and sc.reg_created >= to_date('2008-01-01 00:00', 'YYYY-MM-DD HH24:MI')

What's the deal?
I want those zero rows.

GungHo

If the dates are stored in the table you have outer joined, you aren't seeing the ones with a >= date because the date data for those rows is NULL in an outer joined table. NULL rows won't get picked up by a >=. If you want to pick up those, you'll also have to look for the dates where the date isnull.