Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
use 'SET' instead of 'LET'
Regards
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.
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
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.
That means the the variable last_reload is empty.
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.
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.
Thanks. Got it now.