Solved

ORA-00918: column ambiguously defined

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Determine next b-weekly date 12 57
Oracle Subquery bad Join 11 46
sql for Oracle views 8 38
unable to get sorting resultset 15 48
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

14 Experts available now in Live!

Get 1:1 Help Now