Incremental Load:

[#1]

Hi,

I am using below script for incremental load. Issue is happening while checking for Modified records.

I have verified date formats of 'modified_date' column from database and last update variable. Please check the attached image.

Please help to resolve the issue.

Script:

//Load from QVD

inc1:

LOAD TICKET_NUM,

     APPLICATION,

    "ASSIGNED_TO",

    DESCRIPTION,

    "TICKET_STATUS",

    "OPEN_DATE",

    "CLOSE_DATE",

    MODIFIED_DATE

FROM

[...\abcd.qvd]

(qvd);

Last_Update:

LOAD Max(MODIFIED_DATE) as maxdate

resident inc1;

//Store max update date

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'MM/DD/YYYY hh:mm:ss TT');

DROP Table inc1;

//Incremental Load

INC2:

SQL SELECT "TICKET_NUM",

    APPLICATION,

    "ASSIGNED_TO",

    DESCRIPTION,

    "TICKET_STATUS",

    "OPEN_DATE",

    "CLOSE_DATE",

    MODIFIED_DATE

FROM "GCOR_DI".GREDOPS

Where MODIFIED_DATE >$(Last_Update_Date);

Concatenate

LOAD TICKET_NUM,

     APPLICATION,

    "ASSIGNED_TO",

    DESCRIPTION,

    "TICKET_STATUS",

    "OPEN_DATE",

    "CLOSE_DATE",

    MODIFIED_DATE

FROM

[...\gredops.qvd]

(qvd)

WHERE NOT EXISTS (TICKET_NUM);

//Store into QVD

STORE INC2 into gredops.qvd(qvd);

//Drop INC2

DROP Table INC2;

ERROR Message:

SQL##f - SqlState: S1000, ErrorCode: 933, ErrorMsg: [Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended

When filter is put under quotes

Where MODIFIED_DATE >'$(Last_Update_Date)';

Error Message is:

SQL##f - SqlState: S1000, ErrorCode: 1843, ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month.

[MK_QSL]

Try

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'M/DD/YYYY hh:mm:ss TT');

or

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'M/D/YYYY hh:mm:ss TT');

[Chanty4u]

try with dis?

Where MODIFIED_DATE >='$(Last_Update_Date)';

or

WHERE ModificationTime >='$(LastExecTime)'

  AND ModificationTime <'$(Last_Update_Date)';

[kaushiknsolanki · Accepted solution]

Hi,

Make sure that Database takes date in 'MM/DD/YYYY hh:mm:ss TT' format.

What I can see is that Your variable is holding date format as 'DD/MM/YYYY hh:mm:ss TT' and thus the Sql gives you error that 30(From the variable) is not a valid month.

Regards,

Kaushik Solanki

[#5]

Hi Manish,

Thanks for your response, but it didn't work.

Error is: SQL##f - SqlState: S1000, ErrorCode: 1843, ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month.

[Kushal_Chawda]

try this

Let Last_Update_Date = timestamp(peek('maxdate',0,'Last_Update'),'MM/DD/YYYY hh:mm:ss TT');

Check the Format of Variable in inputbox it should be in MM/DD/YYYY hh:mm:ss TT, then try below where condition

Where MODIFIED_DATE > to_char('$(Last_Update_Date)','MM/DD/YYY HH:MI:SS AM')

[#7]

This time I am getting SQL##f - SqlState: 22005, ErrorCode: 1722, ErrorMsg: [Oracle][ODBC][Ora]ORA-01722: invalid number

[Kushal_Chawda]

Are you sure that MODIFIED_DATE format in database is MM/DD/YYYY hh:mm:ss TT?

if yes, then what is the value in Last_Update_Date variable, Just call the variable in text object & send the screenshot.

[#9]

[Kushal_Chawda]

try this

Where MODIFIED_DATE > to_date('$(Last_Update_Date)','MM/DD/YYY HH:MI:SS AM')

make sure that MODIFIED_DATE is not a string in database if so, try

Where to_date(MODIFIED_DATE,'MM/DD/YYY HH:MI:SS AM') > to_date('$(Last_Update_Date)','MM/DD/YYY HH:MI:SS AM' )