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: 20 Feb 2007 05:47:23 -0800
Message-ID: <1171979243.628269.77990@v45g2000cwv.googlegroups.com>


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

Original text of this message

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