I have a view that runs when I’m in the Editor but gives me an ORA-00918 column ambiguously defined error. All of my columns have table names so I don’t see where it could be going wrong. One other thing, this view has been in use for at least a year, working correctly. No changes have been done on it recently and it worked as early ago as last month this time. It is a union query also. I was able to put the query in a reply.
SELECT GMDDFIN3_630_NEW.TYPEA,
GMDDFIN3_630_NEW.COMPANY,
CAST(GMDDFIN3_630_NEW.SALES_ORDER AS INT) AS SALES_ORDER,
GMDDFIN3_630_NEW.NET_AMT,
GMDDFIN3_630_NEW.NET_AMT_EURO,
GMDDFIN3_630_NEW.STATISTICAL_PERIOD,
GMDDFIN3_630_NEW.YEARWEBI,
GMDDFIN3_630_NEW.CUSTOMER,
GMDDFIN3_630_NEW.REGION,
GMDDFIN3_630_NEW.LINE_OF_BUSINESS,
GMDDFIN3_630_NEW.AREA,
GMDDFIN3_630_NEW.SALES_CHANNEL,
CAST(GMDDFIN3_630_NEW.SALES_REP AS INT) AS SALES_REP,
GMDDFIN3_630_NEW.ADJ_VOL,
GMDDFIN3_630_NEW.STD_COST AS STD_COST,
GMDDFIN3_630_NEW.STD_COST_EURO AS STD_COST_EURO,
0 AS EQUIP_COST,
0 AS EQUIP_COST_EURO,
0 AS INV_ADJ,
0 AS INV_ADJ_EURO,
0 AS PPV,
0 AS PPV_EURO,
0 AS DIST_COST,
0 AS DIST_COST_EURO
FROM BAAN.GMDDFIN3_630_NEW GMDDFIN3_630_NEW
UNION ALL
SELECT GMDDINT_630_NEW.TYPEA,
GMDDINT_630_NEW.COMPANY,
CAST(GMDDINT_630_NEW.SALES_ORDER AS INT) AS SALES_ORDER,
CASE WHEN GMDDINT_630_NEW.NET_AMT IS NULL THEN 0 ELSE GMDDINT_630_NEW.NET_AMT END AS NET_AMT,
CASE WHEN GMDDINT_630_NEW.NET_AMT_EURO IS NULL THEN 0 ELSE GMDDINT_630_NEW.NET_AMT_EURO END AS NET_AMT_EURO,
GMDDINT_630_NEW.STATISTICAL_PERIOD,
GMDDINT_630_NEW.YEARWEBI,
GMDDINT_630_NEW.CUSTOMER,
GMDDINT_630_NEW.REGION,
GMDDINT_630_NEW.LINE_OF_BUSINESS,
GMDDINT_630_NEW.AREA,
GMDDINT_630_NEW.SALES_CHANNEL,
CAST(GMDDINT_630_NEW.SALES_REP AS INT) AS SALES_REP,
CASE WHEN GMDDINT_630_NEW.ADJ_VOL IS NULL THEN 0 ELSE GMDDINT_630_NEW.ADJ_VOL END AS ADJ_VOL,
CASE WHEN GMDDINT_630_NEW.STD_COST IS NULL THEN 0 ELSE GMDDINT_630_NEW.STD_COST END AS STD_COST,
CASE WHEN GMDDINT_630_NEW.STD_COST_EURO IS NULL THEN 0 ELSE GMDDINT_630_NEW.STD_COST_EURO END AS STD_COST_EURO,
CASE WHEN GMDDINT_630_NEW.EQUIP_COST IS NULL THEN 0 ELSE GMDDINT_630_NEW.EQUIP_COST END AS EQUIP_COST,
CASE WHEN GMDDINT_630_NEW.EQUIP_COST_EURO IS NULL THEN 0 ELSE GMDDINT_630_NEW.EQUIP_COST_EURO END AS EQUIP_COST_EURO,
CASE WHEN GMDDINT_630_NEW.INV_ADJ IS NULL THEN 0 ELSE GMDDINT_630_NEW.INV_ADJ END AS INV_ADJ,
CASE WHEN GMDDINT_630_NEW.INV_ADJ_EURO IS NULL THEN 0 ELSE GMDDINT_630_NEW.INV_ADJ_EURO END AS INV_ADJ_EURO,
CASE WHEN GMDDINT_630_NEW.PPV IS NULL THEN 0 ELSE GMDDINT_630_NEW.PPV END AS PPV,
CASE WHEN GMDDINT_630_NEW.PPV_EURO IS NULL THEN 0 ELSE GMDDINT_630_NEW.PPV_EURO END AS PPV_EURO,
CASE WHEN GMDDINT_630_NEW.DIST_COST IS NULL THEN 0 ELSE GMDDINT_630_NEW.DIST_COST END AS DIST_COST,
CASE WHEN GMDDINT_630_NEW.DIST_COST_EURO IS NULL THEN 0 ELSE GMDDINT_630_NEW.DIST_COST_EURO END AS DIST_COST_EURO
FROM BAAN.GMDDINT_630_NEW GMDDINT_630_NEW
I don’t see anything wrong per se in the query. I would suggest running smaller versions of it, adding one column at a time, to try to debug the error.
e.g. start with
SELECT * FROM BAAN.GMDDFIN3_630_NEW GMDDFIN3_630_NEW ;
any errors?
SELECT * FROM BAAN.GMDDINT_630_NEW GMDDINT_630_NEW ;
any errors?
SELECT GMDDFIN3_630_NEW.TYPEA
FROM BAAN.GMDDFIN3_630_NEW GMDDFIN3_630_NEW
UNION ALL
SELECT GMDDINT_630_NEW.TYPEA
FROM BAAN.GMDDINT_630_NEW GMDDINT_630_NEW ;
any errors?
and so on.
Are BAAN.GMDDFIN3_630_NEW and BAAN.GMDDINT_630_NEW Oracle tables or Oracle views?
Hi Jacques,
Yes, I tried the first two queries before I posted my thread. The first and second queries do not produce any errors. I then tried running them with one field (typea) and I get column ambiguously defined. I just think the strangest thing about it is if I run it in the editor I get no errors. I only get errors if I run it in the query builder.
The only difference between the Query Builder and the editor is the scope of the session. IE: What is the current schema of the session. The Query Builder uses the default schema of the connection. The Editor uses the schema that the object explorer is looking at. Try changing the default schema of your connection.
Hi Debbie,
Thank you for the reply. I wasn’t exactly sure how to change the default schema of the connection. I went into the properties of the Oracle connection and found schema in there. I connected it to BAAN which I generally browse to once I’m connnected anyway and then ran the query in the query builder again with no luck. I still get the ORA-00918 error.
If I run the query above (the union query) it runs fine from the editor. I have created a view based on the above SQL query (just put a create or replace view in front of the union above). If I put the resulting query into the query builder, that fails with ORA-00918.
For us to look into it further i would need to have the DDL to all of the tables in the query and a copy of the query. With that we could try to replicate the issue. It is up to you if you would like us to look into it further.
Yes, I absolutely would like you to look at it further. What’s the best way to get you all of the things you’re asking for. The query is quite complex with several layers and many tables.
To look at it further we need DDL for all tables in the query so we can recreate the problem on a local instance.