Get Yesterday's Data from Oracle into SQL

[tsinc]

Hi Folks,

I am trying to add a SQL statement to a DTS job (run nightly) to just bring over data that populated an Oracle table the day before. I have a statement that does this between SQL db's, but using the same statement with Oracle is crapping out.

Statement as follows:

SELECT *
FROM table_name
WHERE datepart(dy,table_name.date_field) = datepart(dy,dateadd(dy,-1,GETDATE()))

The error I'm getting when parsing the statement is:

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: "DATEPART":invalid identifier

The date format from the field I am querying is YYYY-MM-DD HH:MM:SS.

Any help is greatly appreciated ... thanks!!!

 

[Catadmin]

Oracle doesn't like T-SQL, which is what your problem probably is. It wants Oracle SQL and since I don't know what Oracle SQL is, I can't give you the proper command.

You're probably going to have to create a job on the Oracle DB to export the data to a flat file and then pick it up from the flat file with a SQL import.

If anyone has a better idea, please post.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

You need to take your select statement and convert it to Oracle's SQL language. Commands such as datepart and getdate() aren't valid Oracle Commands.

It's been a while since I've done Oracle, but something along these lines should do the trick. Check the Oracle forums for the correct syntax.

select *
from table_name
WHERE table_name.date_field = (select date-1 from dual);

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tsinc]

Thanks guys ... I took your advice and checked the Oracle forums for syntax. Modified my statement as follows:

WHERE table_name.date_field = (trunc(sysdate)-1)

Ran it and it's working like a charm ... thanks again for the direction ...