Issue with Incremental Loading in the load script

[#1]

Hi,

I am creating a variable as shown -

LET INCREMENTAL_EXP='where trunc(pxcreatedatetime)>=to_date('&Chr(39)&FieldValue($(last_reload),1)&Chr(39)&',''MM/DD/YYYY'')
or trunc(pxupdatedatetime)>=to_date('&Chr(39)&FieldValue($(last_reload),1)&Chr(39)&',''MM/DD/YYYY'')'; 

In the debugger, I see, it is the date value and it doesn't show any error.  Then, I am trying to use this variable in my Oracle SQL query as shown below -

select * from guidelines.beacon_mg_attest where case_id in (select pyid from beacon.cases $(INCREMENTAL_EXP));

Here, when $(INCREMENTAL_EXP) gets resolved, the date portion is blank.  Please help me to understand what I am missing here.

Thanks,

Sudha.

[fredericmante]

use 'SET' instead of 'LET'

Regards

[#3]

I had tried that and it is not working.  If I use SET, the variable within the variable is not getting evaluated at run time.

Thanks,

Sudha.

[Gysbert_Wassenaar]

Try using single quotes around the dollar expansion of the variable:

select * from guidelines.beacon_mg_attest where case_id in (select pyid from beacon.cases '$(INCREMENTAL_EXP)');

And make sure the variable last_reload contains a string value in the date format MM/DD/YYYY

[#5]

Tried that...still not working.

This is what I see at run time, when I used the quotes you had suggested -

select * from guidelines.beacon_mg_attest where case_id in (select pyid from beacon.cases 'where trunc(pxcreatedatetime)>=to_date('''',''MM/DD/YYYY'')

                            or trunc(pxupdatedatetime)>=to_date('''',''MM/DD/YYYY'')')

Thanks,

Sudha.

[Gysbert_Wassenaar]

That means the the variable last_reload is empty.

[#7]

When I run in debug mode, I can see it is getting evaluated correctly in the variable INCREMENTAL_EXP but when trying to use the variable in the query, it does not.  Please have a look at the attachment.

Thanks,

Sudha.

[Gysbert_Wassenaar]

The fieldvalue function only works on fields, not variables. So fieldvalue($(last_variable), 1) will return nothing since there isn't a field with the name 42459.614351852 or 3/30/2016 2:44:40 PM.

[#9]

Thanks.  Got it now.