Forum Stats

  • 3,836,816 Users
  • 2,262,196 Discussions


Trouble with date variables

713539 Member Posts: 19
edited Jul 24, 2009 10:39AM in Data Integrator
I have an Oracle Source database and a SQL Server Target database. I need to do the following:

1. Get Max Date from target SQL Server table and assign to a variable
2. Get Max Date from source Oracle table and assign to a variable
3. Create a filter on Oracle source table that includes the statement "Prod_Date >= #Target_Max_Date - #Source_Max_Date"

Variables are Global and are of type "date". My test using one or both variables returne the error: "ORA-00911: invalid character". I know if I write a sub-query to test this in TOAD it works (ie: where Prod_Date <= (select max(Prod_Date)...."

Prod_Date is obviously type "Date".

Suggestions anyone?


  • 708544
    708544 Member Posts: 31
    Hi Becker,

    First of all the difference of two dates is a number hence following logic fails as you are comparing a date (Prod_Date) with a number(Target_Max_Date - #Source_Max_Date)......
    Prod_Date >= #Target_Max_Date - #Source_Max_Date

    and Prod_Date is obviously type "Date" as you said....

    Can u be more specific what u want to do??
  • 713539
    713539 Member Posts: 19
    Ug! My statement should have been "Prod_Date >= Prod_Date - (#Target_Max_Date - #Source_Max_Date)" with some extra logic also. The full statement in my code is:

    "Prod_Date >= Prod_Date - (#FDC_WELL_PROD_EST_Max_Date - #Well_Prod_Daily_Max_Date) - 45"

    What I am attempting to do is retrieve all records from source that are the sum of the source_date minus the target date minus 45 days. Sorry for the big omission the first time.
  • 708544
    708544 Member Posts: 31
    edited Jul 24, 2009 9:26AM
    Hi Becker,

    Try to use as

    Prod_Date >= Prod_Date - ('#FDC_WELL_PROD_EST_Max_Date' - '#Well_Prod_Daily_Max_Date') - 45


    Please mark as helpful/correct for our motivation.....* ;)
  • Shamil
    Shamil Member Posts: 181
    Check in Operator tool the format of date your variables store. And apply the correct formating to your formula, i.e.

    "Prod_Date >= Prod_Date - (TO_DATE('#FDC_WELL_PROD_EST_Max_Date','DDMMYYYY') - TO_DATE('#Well_Prod_Daily_Max_Date','DDMMYYYY')) - 45"
  • 713539
    713539 Member Posts: 19
    edited Jul 24, 2009 10:14AM
    Where in operator do I check the format of the date?

    Also, I have tried using the following formatting:

    Prod_Date >= Prod_Date - ('#FDC_WELL_PROD_EST_Max_Date' - '#Well_Prod_Daily_Max_Date') - 45

    Result: ORA-01722: invalid number

    A date is a number, so any ideas why the variable is being considered a number?


    Prod_Date >= Prod_Date - (TO_DATE('#FDC_WELL_PROD_EST_Max_Date','DDMMYYYY') - TO_DATE('#Well_Prod_Daily_Max_Date','DDMMYYYY')) - 45

    Result: ORA=-01858: a non-numeric character was found where a numeric was expected.

    In this scenario, it would seem we are trying to convert a date to a date?

    Appreciate your responses.

    Edited by: Becker on Jul 24, 2009 7:13 AM
  • Shamil
    Shamil Member Posts: 181
    In Operator tool, after expanding the session, just befote the step list you-ll find "Variables" grouping. Exand it and edit and doubleclick on the variable. In the dialogbox go to Historical tab and check the values assigned.

    I would suggest you to go another way:
    1. Make sure both of your variables are of alphanumeric type.
    2. While assigning max dates to your variables put them into the desired format, i.e. for Oracle TO_CHAR(max(column_name),'DDMMYYYY')

    Hope it helps,
This discussion has been closed.