Solved

ORA-00918: column ambiguously defined

Posted on 2014-09-10
3
885 Views
Last Modified: 2014-09-11
I am attempting to do a where clause to filter data between dates, my where clause currently states:

AND ACCOUNTING_DATE >= '10/09/2013'
AND ACCOUNTING_DATE <= '10/09/2014'

This is the date format for the accounting date dd/mm/yyyy. However I keep getting ORA-00918: column ambiguously defined. Where am I going wrong?
0
Comment
Question by:pma111
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 167 total points
ID: 40314268
what is the rest of your query?

if the problem is with accounting_date it's because you're joining multiple tables that have a column called accounting_date.

The error has nothing to do with your formats.

However, if your column is a date, then you should use dates not strings.

accounting_date >= to_date('10/09/2013','mm/dd/yyyy')
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 167 total points
ID: 40314274
please read up this article, you need to use aliases for your tables, and apply them on the column names :
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html

the issue about the date 'formats' mentioned above is also described in this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
ID: 40314519
James is the guilty one!

Ah! but which James?

It's the same with columns, these are ambiguous because there is more than one table with a field of the same name, so the parser does not know which table it is supposed to use.

You should prefix each field with the table, or table alias, that it comes from.

---
By the way:
this is one year and a bit:
AND alias.ACCOUNTING_DATE >= '10/09/2013'
AND alias.ACCOUNTING_DATE <= '10/09/2014'

this is EXACTLY one year:
AND alias.ACCOUNTING_DATE >= '10/09/2013'
AND alias.ACCOUNTING_DATE <  '10/09/2014' -- notice there is no equals here
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question