Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00918 on UNIONS
ORA-00918 on UNIONS [message #218548] Thu, 08 February 2007 10:57 Go to next message
cshamoh
Messages: 5
Registered: February 2007
Junior Member
Guys,

Im getting strange things here.
Im doing some ETL prototype for Siebel reporting.
There is fact table, dim tables, aggregation calculations etc.
Thing is that Im getting this nasty 'ORA-00918: column ambiguously defined error' nomatter what I do on making final UNIONs on 4 views.

SELECT * 
FROM ((SELECT a.PROD_ID_A, a.CUST_ID_A, a.GEO_ID_A, a.TIME_ID_A, a.FACT_AMT_2_CORR_LOW_A, a.FACT_AMT_2_CORR_HIG_A, a.SKU_FACT_AMT_2_A, a.FACT_AMT_2_INDEX_A, a.FACT_AMT_2_INDEX_STATUS_A FROM pp_np_cmp_cn_amt2_lm_v a
UNION
SELECT b.PROD_ID_C, b.CUST_ID_C, b.GEO_ID_C, b.TIME_ID_C, b.FACT_AMT_2_CORR_LOW_C, b.FACT_AMT_2_CORR_HIG_C, b.SKU_FACT_AMT_2_C, b.FACT_AMT_2_INDEX_C, b.FACT_AMT_2_INDEX_STATUS_C FROM pp_np_cmp_cn_amt2_l3m_v b)
UNION
(SELECT c.PROD_ID_E, c.CUST_ID_E, c.GEO_ID_E, c.TIME_ID_E, c.FACT_AMT_2_CORR_LOW_E, c.FACT_AMT_2_CORR_HIG_E, c.SKU_FACT_AMT_2_E, c.FACT_AMT_2_INDEX_E, c.FACT_AMT_2_INDEX_STATUS_E FROM pp_np_cmp_cn_amt2_l6m_v c
UNION
SELECT d.PROD_ID_G, d.CUST_ID_G, d.GEO_ID_G, d.TIME_ID_G, d.FACT_AMT_2_CORR_LOW_G, d.FACT_AMT_2_CORR_HIG_G, d.SKU_FACT_AMT_2_G, d.FACT_AMT_2_INDEX_G, d.FACT_AMT_2_INDEX_STATUS_G FROM pp_np_cmp_cn_amt2_fytd_v d));


Strange thing is that when Im doing UNION for any (any combination) of those 3 selects - it works, but as I add 4th it fails.

I tried everything - tables aliases, renaming columns with 'AS' etc. Still the same...

Thanx for saving my project Wink

Cshamoh



[Updated on: Thu, 08 February 2007 10:59]

Report message to a moderator

Re: ORA-00918 on UNIONS [message #218568 is a reply to message #218548] Thu, 08 February 2007 13:11 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
What happens if you get rid of all of the parenthesis and the outer select * from? Logically, union really doesn't have an order of precedence, because union says take them all.

Re: ORA-00918 on UNIONS [message #218580 is a reply to message #218568] Thu, 08 February 2007 15:14 Go to previous messageGo to next message
cshamoh
Messages: 5
Registered: February 2007
Junior Member
Same thing Sad
That outer select and parenthesis was my act of desperation Smile
I have started with
select * from ...
UNION
select * from ...
UNION
...


and as long there are only 3 unions it works...
I do not get it.
Re: ORA-00918 on UNIONS [message #218614 is a reply to message #218580] Fri, 09 February 2007 00:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
what happens if you execute the fourth query as stand-alone query?
Re: ORA-00918 on UNIONS [message #218626 is a reply to message #218614] Fri, 09 February 2007 00:54 Go to previous messageGo to next message
cshamoh
Messages: 5
Registered: February 2007
Junior Member
It works.
As I said, I can choose and UNION any 3 queries and get no error, but when I do it with all of them, I get 00918...
Re: ORA-00918 on UNIONS [message #218715 is a reply to message #218548] Fri, 09 February 2007 09:21 Go to previous message
cshamoh
Messages: 5
Registered: February 2007
Junior Member
Issue 'workarounded'.
I have changed one of the views into table and it helped.
Seems like Oracle is not 100% flexible and if there is too many views in the row and it gets confused.

Anyway thnx for your time Smile

[Updated on: Fri, 09 February 2007 09:22]

Report message to a moderator

Previous Topic: how to delete incorrect data within a table and change it to the correct one.Please help me.
Next Topic: Help needed
Goto Forum:
  


Current Time: Mon Dec 05 23:58:58 CST 2016

Total time taken to generate the page: 0.10846 seconds