none
ORA-00904 "SYSDATE INVALID IDENTIFIER"

    Question

  • Hi All

    I'm using a SQL command to load data from ORACLE to a SQL SERVER Table. Its a simple query which brings data from the previous day, for this I used a simple logic SYSDATE-StartDate=1. When I run this sql query in SQL Developer it works great, but when I use this same query in SSIS Package I'm getting the ORA-00904 SYSDATE Invalid Identifier error message. Please see the sql below

    SELECT     *
    FROM         testvw
    WHERE     trunc(SYSDATE) - TO_DATE(StartDate, 'DD-MON-YY') = 1

    Any ideas where I'm doing it wrong?

    Thanks

    Sunday, July 13, 2014 2:01 PM

Answers

  • Finally i solved it. Instead of SYSDATE i used trunc(current_date)-trunc(StartDate)=1 and it worked ... :-). Thaks a lot all.

    • Edited by AmyBI Monday, July 14, 2014 6:58 PM
    • Marked as answer by AmyBI Monday, July 14, 2014 6:58 PM
    Monday, July 14, 2014 3:42 PM

All replies

  • Check the case on StartDate vs. the source table. Depending on how the table is created, the column name may be case sensitive - or not. SQL Developer may be doing something behind the scenes to mess with you, or SSIS may be. (like embedding the column name in double quotes, which can trip you up.)

    In short, if you create a column in Oracle as all caps, it will not be case sensitive.

    If you create a column in mixed case without double quotes, it will not be case sensitive.

    If you create a column in other than all caps but enclosed in double quotes, it is case sensitive.

    Use DESCRIBE {table_name} in SQL developer and you'll see the original column name. Match the case to what you see.

    Monday, July 14, 2014 2:18 AM
  • Hi All

    I'm using a SQL command to load data from ORACLE to a SQL SERVER Table. Its a simple query which brings data from the previous day, for this I used a simple logic SYSDATE-StartDate=1. When I run this sql query in SQL Developer it works great, but when I use this same query in SSIS Package I'm getting the ORA-00904 SYSDATE Invalid Identifier error message. Please see the sql below

    SELECT     *
    FROM         testvw
    WHERE     trunc(SYSDATE) - TO_DATE(StartDate, 'DD-MON-YY') = 1

    Any ideas where I'm doing it wrong?

    Thanks

    Please see if you can run the same query by Openquery through Linked server instead.

    Many Thanks & Best Regards, Hua Min

    Monday, July 14, 2014 3:00 AM
  • Thanks Ernest, its not the problem with the StartDate. When i remove the SYSDATE from the query i can get results for the StartDate. The problem arises when i use the SYSDATE in the WHERE clause.

    Thanks

    Monday, July 14, 2014 12:14 PM
  • Thanks HuaMin Chen. I'm very new to this. Is there a document or link which shows how to do this?
    Monday, July 14, 2014 12:31 PM
  • AmyBI - What is the datatype of StartDate?
    Monday, July 14, 2014 2:08 PM
  • DATE
    Monday, July 14, 2014 2:26 PM
  • Scratching my head here. I try a similar query with an Oracle data source. Works in SQL Developer, but doesn't work from SSIS - with a different error message.

    But this method does work in either when StartDate is data type DATE:

    SELECT     *
    FROM         testvw
    WHERE     trunc
    (SYSDATE) - trunc(StartDate) = 1

    Monday, July 14, 2014 2:38 PM
  • Finally i solved it. Instead of SYSDATE i used trunc(current_date)-trunc(StartDate)=1 and it worked ... :-). Thaks a lot all.

    • Edited by AmyBI Monday, July 14, 2014 6:58 PM
    • Marked as answer by AmyBI Monday, July 14, 2014 6:58 PM
    Monday, July 14, 2014 3:42 PM
  • No luck i tried

    TRUNC(SYDATE)-TRUNC(StartDate)=1 .

    I tried TRUNC(StartDate)>=12-dec-12 and it pulled the records. Some thing going with the SYSDATE.

    Monday, July 14, 2014 5:35 PM