OraFAQ Forum: Other Operating Systems » Datediff translation in 10G

Home » Infrastructure » Other Operating Systems » Datediff translation in 10G

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Datediff translation in 10G [message #224072]

Mon, 12 March 2007 18:45

rojo72
Messages: 7
Registered: March 2007

Junior Member

Could you please help me translate this SQL query to a functional Oracle script? The table name is mmobjs that we are referencing: I initially received the following error message: ORA-00904 "Datediff" invalid identifier.

SQL Query to translate to Oracle:

mmobjs.type=0 and mmobjs.object=1 and datediff (hour, emp.lastchanged, getdate() ) <= 24

Report message to a moderator

Re: Datediff translation in 10G [message #224134 is a reply to message #224072]

Tue, 13 March 2007 03:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If "datediff" subtracts two dates, you'll have to subtract them:

AND date_column_1 - date_column_2 <= 24

The result is number of DAYS. If you want to "translate" it to hours, multiply the result with 24.

Furthermore, if "getdate()" function returns current date (and time), Oracle uses "sysdate" for such a purpose. So, your query might finally look like this:

AND (sysdate - date_column) * 24 <= 24

Report message to a moderator

Re: Datediff translation in 10G [message #224266 is a reply to message #224072]

Tue, 13 March 2007 11:32

rojo72
Messages: 7
Registered: March 2007

Junior Member

Thanks for the reply.

The script's function is to pull the data from the mmobjs table that has been changed in the past 24hrs referencing the emp table for when the records have last been changed.

When I added sysdate to my script it returns the error:

ORA-00920 Invalid relational operator

Would you have any further suggestions?

Report message to a moderator

Re: Datediff translation in 10G [message #224289 is a reply to message #224266]

Tue, 13 March 2007 14:14

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This is what Oracle says about it:

Oracle

ORA-00920 invalid relational operator

Cause: A search condition was entered with an invalid or missing relational operator.

Action: Include a valid relational operator such as =, !=, ^=, <>, >, <, >=, <=, ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE in the condition.

If you can't find the solution, could you post the entire query so that we could see it and, probably, find the way to make it work?

Report message to a moderator

Re: Datediff translation in 10G [message #225851 is a reply to message #224289]

Wed, 21 March 2007 13:54

rojo72
Messages: 7
Registered: March 2007

Junior Member

I was able to get the script to work but now I am faced with another challenge. The files are currently exporting as EMPID.jpg

When I pull the jpeg files from the table "mmobjs" I need to name the jpeg files with a column from another table. There is a common field in both tables named: "empid"

The goal is to name the jpeg file with the data from the EMP.SSNO table

So the files should be SSNO Number.jpg

Report message to a moderator

Re: Datediff translation in 10G [message #226598 is a reply to message #225851]

Sun, 25 March 2007 15:02

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm not sure I understood it completely, but - you might try with something like this: join those tables and "create" a file name using the concatenation operator:

SELECT m.jpeg_name || TO_CHAR(s.no) ||'.jpg' jpeg_file_name
FROM mmobjs m, ssno s
WHERE m.empid = s.empid;

Report message to a moderator

Previous Topic:	DB2 to Oracle conversion on OS/390
Next Topic:	Oracle Access Manager AS/400

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 13 06:00:49 CST 2025