Solved

ORA-00918: column ambiguously defined

Posted on 2014-09-10
3
851 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 73

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 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle and DateTime math 6 26
Read only access to a Procedure in oracle? 4 48
execute immediate plsql block 5 34
Create table from select - oracle 6 27
This article describes some very basic things about SQL Server filegroups.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now