Active6 years, 11 months ago
Answer: When ORA-00904 occurs, you must enter a valid column name as it is either missing or the one entered is invalid. The 'invalid identifier' most common.
I am using Oracle DB and trying to find the date difference in years. I googled and find out I can use
DateDiff
function. When I am using this function:I am getting an error
Please suggest what is wrong in this.
Nick Krasnov24.1k44 gold badges4747 silver badges6868 bronze badges
Mohit224Mohit224
1 Answer
DATEDIFF
is not a function that exists in Oracle.What, exactly, does it mean to you to get a difference between two dates in years? For example, if you are given dates of 2010-04-01 and 2012-01-01, what is the expected output? The difference is 1 year and 9 months so would that be 1.75? Or should we count the number of days in those 9 months vs. the number of days in a year and divide the answer to get something slightly different than 1.75?
My first guess is that you want
If you want to ignore any fractional part of a year
Justin CaveJustin Cave195k1919 gold badges306306 silver badges332332 bronze badges
Not the answer you're looking for? Browse other questions tagged oracle10g or ask your own question.
Active5 years ago
When running the query in sqldeveloper, I get the '%s: invalid identifier' error. Basically I want to concatenate two columns from TABLE 1 and compare it with one column in TABLE 2. Two tables are from two different databases. For some reason, it does not like the t1_Name alias.
Any feedback would be appreciated on how I can fix this.
Trinimon12.6k99 gold badges3535 silver badges5757 bronze badges
Alice ChiuAlice Chiu
3 Answers
You cannot use an alias defined in the
SELECT
list of a query in the WHERE
clause. Logically, the WHERE
clause is processed first so the alias doesn't exist yet.You'd either need to replicate the calculation
or you would need to define the alias in an inline view
Justin CaveJustin Cave195k1919 gold badges306306 silver badges332332 bronze badges
If your two tables are in different databases, your have to use a database link. Assume table1 is in database db1 and table2 is in database db2. Your query (assuming you run the query on db1) should look like that:
More information you can find here:http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm
DirkNMDirkNM
Trinimon12.6k99 gold badges3535 silver badges5757 bronze badges