Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-00918: column ambiguously defined
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
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