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

without knowing what your table columns are, I think the outer join operator needs to go with the column:
to_date(to_char(a.thedate(+),'YYYYMM'),'YYYYMM') ?

But better, since count(*) will always be at least 1 in your construction, Consider a UNION with one part giving the counted ones and the other query giving the nulls (IOW the zero counts).

HTH,
  Ed Received on Thu Jan 22 2004 - 13:05:00 CST