Re: Problem with outer join

RaceTrac Petroleum (racetrk1_at_mindspring.com) wrote:
: These are the table that we working with:
 

: Table A:
: key_id Date Value
: 1 04-DEC-95 1000
 
: Table B:
: key_id
: 1
: 2
 

: This SQL statement generate the results posted below:
: This is the result that we want.
 

: select key_id, nvl(sum(value),0)
: from A, B
: where A.key_id = b.key_Id (+)
 

: Results:
: key_id sum(Value)
: 1 1000
: 2 0
 

: The same SQL stmt with the date criteria returns only 1 row.
 

: select key_id, nvl(sum(value),0)
: from A, B
: where A.key_id = b.key_Id (+)
: and date = '04-DEC-95'
 

: Results:
: key_id sum(Value)
: 1 1000
 

: Does any one know a way around this?

--

Try adding a (+) to the constant side of the date comparison.  I know I've
done stuff like this before.

 select key_id, nvl(sum(value),0)
 from A, B
 where A.key_id = b.key_Id (+)
 and date = '04-DEC-95'    (+)



PGY  (Opinions expressed are mine and mine alone)
################################################################
#  Paul Young                                                  #
#  youngpa_at_statcan.ca                                          #
#  Statistics Canada                                           #
################################################################