Re: ORA-00918: column ambiguously defined

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 Received on Tue Feb 20 2007 - 08:07:51 CST