Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-00918: column ambiguously defined

Re: ORA-00918: column ambiguously defined

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 22 Feb 2007 17:25:31 -0800
Message-ID: <1172193931.660675.50260@p10g2000cwp.googlegroups.com>


On Feb 20, 9:07 am, timothy.h..._at_gmail.com wrote:
> On 20 Feb, 13:47, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Feb 20, 8:33 am, timothy.h..._at_gmail.com wrote:
>
> > > Hi,
>
> > > I am getting the above error when I try and run the query below on
> > > bb.tab_urn. However, removing this group by then gives the same error,
> > > but at bb.transaction_date. Unless I have missed something completely,
> > > I am certain I have defined everything correctly. Thoughts? Any help
> > > would be very much appreciated!
>
> > > Cheers
>
> > > Tim
>
> > > select com.name
> > > , curr.quantity
> > > , prev.quantity
> > > from ( select count(*) as quantity
> > > , a.tab_urn
> > > from v_rep_vehicle_check_3m_trans a
> > > where a.CLASS_CODE in ('TDCP', 'TDCC')
> > > and a.TRANSACTION_DATE between
> > > to_date('2007-01-01000000','YYYY-MM-DDHH24MISS')
> > > and
> > > to_date('2007-01-31235929','YYYY-MM-DDHH24MISS')
> > > and a.tab_urn not in (0,10)
> > > group by a.tab_urn) curr
> > > full outer join
> > > ( select count(*) as quantity
> > > , bb.tab_urn
> > > from v_rep_vehicle_check_3m_trans bb
> > > where bb.CLASS_CODE in ('TDCP', 'TDCC')
> > > and bb.transaction_date between
> > > add_months(to_date('2007-01-01000000','YYYY-MM-DDHH24MISS'),-1)
> > > and
> > > add_months(to_date('2007-01-31235929','YYYY-MM-DDHH24MISS'),-1)
> > > and bb.tab_urn not in (0,10)
> > > group by bb.TAB_URN) prev
> > > on prev.tab_urn = curr.tab_urn
> > > , rep_curr_company com
> > > where curr.tab_urn = com.tab_urn
> > > OR prev.tab_urn = com.tab_urn
> > > order by
> > > com.name;
>
> > I am not the most observant person so naturally I did not spot the
> > error. But what version of Oracle is this?
>
> > Try changing quantity to a unique name every where it appears in an
> > inline view and prefix the one or two columns you did not prefix. If
> > doin this does not identify and fix the problem I suggest rewriting
> > the query to use traditional syntax.
>
> > HTH -- Mark D Powell --
>
> Hi Mark,
>
> Thanks for your quick reply. The version I am using is Oracle 10g. I
> have tried what you suggested, but no joy. You say traditional syntax
> - could you give me an example? Whilst I can use SQL to a certain
> degree, I don't think I am as competent as I would like (illustrated
> by my query I think!) So any help anyone can offer would be great!
>
> Cheers
>
> Tim- Hide quoted text -
>
> - Show quoted text -

Similar to
from table1 a, table2 b, table3 c
where a.key = b.key(+)
and a.key = c.key(+)

You just list the tables in the FROM clause and you just list the ON conditions in the WHERE clause. The plus sign in () is the traditional outer join symbol in Oracle. The plus goes on the side (table) which returns NULL when there is no match so that the receiving table on the other side of the relational operator alwyas returns a row.

HTH -- Mark D Powell -- Received on Thu Feb 22 2007 - 19:25:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US