Home » Infrastructure » Other Operating Systems » Datediff translation in 10G
Datediff translation in 10G [message #224072] Mon, 12 March 2007 18:45 Go to next message
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
Re: Datediff translation in 10G [message #224134 is a reply to message #224072] Tue, 13 March 2007 03:53 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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
Re: Datediff translation in 10G [message #224266 is a reply to message #224072] Tue, 13 March 2007 11:32 Go to previous messageGo to next message
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?



Re: Datediff translation in 10G [message #224289 is a reply to message #224266] Tue, 13 March 2007 14:14 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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?
Re: Datediff translation in 10G [message #225851 is a reply to message #224289] Wed, 21 March 2007 13:54 Go to previous messageGo to next message
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

Re: Datediff translation in 10G [message #226598 is a reply to message #225851] Sun, 25 March 2007 15:02 Go to previous message
Littlefoot
Messages: 21806
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;
Previous Topic: DB2 to Oracle conversion on OS/390
Next Topic: Oracle Access Manager AS/400
Goto Forum:
  


Current Time: Thu Mar 28 08:38:21 CDT 2024