Solved

ORA-00918: column ambiguously defined

Posted on 2014-09-10
3
894 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

738 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