Re: Outer Joins with standard Oracle functions?

John <Nothanks_at_noaddress.com> wrote in message news:<Xns94787AF064DNothanksnoaddresscom_at_127.0.0.1>...
> I'm trying to produce a query that will give me a count of records for each
> month for the past 12 months, defaulting to 0 if there are no records.
>
> I have a subquery to produce the month beginnings (table not possible atm).
> The records in the table I'm counting have a full date/time.
>
> My query is...
>
> select company,monthbeginning,count(*)
> from mytable a,
> monthbeginningssubquery b
> where b.date=to_date(to_char(a.thedate,'YYYYMM'),'YYYYMM')(+)
> group by company,monthbeginning
>
> Unfortunately the outer join isnt accepted by oracle, because it's on a
> to_date.
>
> Any suggestions? I dont have permissions to write a package/function to
> handle this, have to be pure sql. This is Oracle 8.1.7.latest STD ed. If
> there's a better way to do this kinda thing I'm all ears!
>
> Thanks
> J

select b.begin_month, count(a.object_id) from all_objects a,
(select add_months(trunc(sysdate, 'MM'), (-1)*rownum) begin_month from all_objects where rownum <= 12) b
where b.begin_month = trunc(a.created(+), 'MM') group by b.begin_month

count a "not null" column in "mytable" will do that. Received on Fri Jan 23 2004 - 09:04:15 CST