Calculated SQL text / ODBC Import Script Step - Error

[ScottVan]

I have a script that collects the last update date of a set of records. I set a local variable to it ($LastImport).

 

  Set Variable $LastImport  =xyz_0::MAX_LAST_UPDATE

 

I setup a Import Records script step that goes out to an ODBC database (Oracle 10g) to bring into a temp table only those records that are >= than the last update date.

 

   "SELECT * FROM xyz_LEVEL_0_DATA_V WHERE xyz_LEVEL_0_DATA_V.LAST_UPDATE_DATE >= " & $LastImport & ""

 

I get this error back:

 

    ODBC Error: [Oracle][ODBC][Ora] ORA-00936: missing expression

 

I have imported data from this Oracle database many times directly into tables but this is the first time I'm trying to filter down the import to make it execute faster (over 4,000,000 records to deal with )...

 

Any ideas?

 

Thanks

 

Scott

[Wim Decorte]

SQL will expect the $lastImport date to be in a certain format.  Chances are that it is not the format that you have in your FM file.  Check into that first and see how you can reformat the FM date into the SQL format that Oracle expects.

[ScottVan]

This works fine:

 

"SELECT * FROM xyz_LEVEL_0_DATA_V WHERE xyz_LEVEL_0_DATA_V.LAST_UPDATE_DATE >= TRUNC(SYSDATE)-1"

 

The trick seems to properly indicate the variable in the calculated text.

[ScottVan]

To help alleviate anyone else creating flat spot on their head from desk banging...

 

 

"SELECT * FROM xyz_LEVEL_0_DATA_V WHERE xyz_LEVEL_0_DATA_V.LAST_UPDATE_DATE > TO_DATE(" & $LastImport & ", 'MM/DD/YYYY')"