Re: Inner join with where

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sun, 6 Feb 2005 20:52:23 -0500
Message-ID: <I6KdnUhq9I9GVJvfRVn-hg_at_comcast.com>


"Nerfherder" <nobody_at_home.com> wrote in message news:33gd015g73geljerllut1hmer5uruv05rv_at_4ax.com...
>I have the following SQL statement I need to
> add a secondary where clause to
>
> SELECT name, amount
> FROM node, log
> WHERE log.name (+) = node.name
>
> Now I need to add run_date = '31-JAN-05',
> I can not just add this statement since when I do
> loose columns in the node table since there is
> not a 1:1 match between the two tables.
>
>

which table is the run_date column in?

bottom line, you have to include the oracle's outer join syntax in all affected predicates, so, depending on which table RUN_DATE is in, you need to add either

run_date = to_date('31-JAN-05','dd-mon-rr') (+)

or

run_date(+) = to_date('31-JAN-05','dd-mon-rr')

note also that you should never use implicite date conversion -- either use TO_CHAR or the ansi date literal syntax (if using 10g)

++ mcs Received on Mon Feb 07 2005 - 02:52:23 CET

Original text of this message