Re: Outer Join Problem . . .

After reading the responses, nothing works. I will clarify a little more. Assuming I have two tables:

em_table1                           em_table2

d_id           d_name               d_date          d_dummy
01             January              01/01/00        Test
02             February             02/01/00        Test
03             March                03/01/00        Test
04             April                04/01/00        Test
05             May                  06/01/00        Test
06             June                 08/01/00        Test
07             July                 12/01/00        Test
08             August
09             September
10             October
11             November
12             December

What I want to do is count the number of distinct d_dummy values per month. For months that have no entries I want to show 0.

If I issue the following:

SELECT em1.d_name,

       COUNT(DISTINCT(em2.d_dummy))
  FROM EM_TABLE1 em1,

       EM_TABLE2 em2
 WHERE em1.d_id = TO_CHAR(em2.d_date, 'MM')  GROUP
    BY em1.d_name

I get:

April	        1
August	        1
December	1
February	1
January	        1
June	        1
March	        1
September	1

Note I am missing some, so I naturally wanted to do this:

SELECT em1.d_name,

       COUNT(DISTINCT(em2.d_dummy))
  FROM EM_TABLE1 em1,

       EM_TABLE2 em2
 WHERE em1.d_id = TO_CHAR(em2.d_date, 'MM') (+)  GROUP
    BY em1.d_name

But this gives me the following:

ORA-00933: SQL command not properly ended.

No matter which side of the to_char I have the plus sign, it fails.

Any suggestions.

In article <8f77bo$vnt$1_at_nnrp1.deja.com>,   emaus081269_at_my-deja.com wrote:
> Having a problem with an outer join. Trying to get a count grouped by
> month from a date field on a table. Want to display 0 for months not
> included in the table. Created a temp table that holds each month to
> outer join too. But oracle does not seem to like outer joining when I
> am useing a function on the table I will be weak on. I can subquery
> this out and it works. Just want to know if anyone knows why or has a
> better solution.
>
> select m.name,
> nvl(count(t.field), 0)
> from tmp_months m,
> table t
> where m.tag = to_char(t.date, 'MM') (+)
> group by to_char(t.date, 'MM')
>
> This gets me a not properly ended message. Take out the outer join
 and
> all is well. Hmm . . .
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 09 2000 - 00:00:00 CDT