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: William Robertson <williamr2019_at_googlemail.com>
Date: 21 Feb 2007 03:14:47 -0800
Message-ID: <1172056487.724197.126880@p10g2000cwp.googlegroups.com>


On Feb 20, 2:07 pm, 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

I can't see an error either but it might be worth putting that final join into ANSI syntax rather than switching back to a where-clause join. You might also try moving the inline views into WITH clauses e.g.

WITH curr AS ( SELECT COUNT(*) AS quantity ... )

   , prev AS ( SELECT COUNT(*) AS quantity ... ) SELECT com.name

     , curr.quantity
     , prev.quantity ...
FROM   curr
     , prev
     , rep_curr_company com

WHERE ... However I'm afraid this is a case of nudging it and seeing what it does rather than getting to the root of the problem. Possibly the CBO is rewriting the query in some way and running into a bug.

Possibly I'm missing something but the join oerder seems a bit odd. You are full outer joining two inline views and then inner joining then to a third table, with an 'or' condition. Shouldn't you start with rep_curr_company and then left outer join each of the two views to that? Received on Wed Feb 21 2007 - 05:14:47 CST

Original text of this message

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