Hi I have installed the latest version which supports the set variable option. I have Created the file and have kept some mumbers as value and created the action of file iterator and export dataset.
But now it is giving the errror as ‘%File Iterator1%’ is not a valid integer value.
I got it to work by changing the variable type to VARCHAR2 and changing ‘439’ in the file to just 439
I will take a look at it with NUMBER but I think you should be able to get it to work with these changes.
Oh, I see you are using IN in your query.
If you are putting one value, use an = instead of IN.
If you really want IN, with multiple values, try it like this, with a substitution variable instead of bind variable, don't forget to add the parenthesis, and get rid of those quotes if "a.location" is a number and not varchar2.
John,
I used substitution variable and tried both IN and = but now it i giving Invalid Character error.
I think you are trying to run the Export Dataset Action.
Select the File Iterator Action, right-click -> Run.
Thanks John, it worked like a charm. And i guess same logic can be applied to make the date field as dynamic and coming from different file?
Yes, just be sure to use the TO__DATE function so that there is no problem converting the date format.
Sorry I have to come back again John as I have used the To_date function but in all the cases i am getting the Input Bind Variable name.
The initial tweak of start date i query is like :
WHERE a.cycle_count = s.cycle_count
AND s.stocktake_date BETWEEN to_date(&&StartDate,‘DD-MON-YYYY’)) AND ‘31-Jul-2018’ and
a.item = x.item and a.item = c.item
and x.status_code = ‘A’
and c.status = ‘A’
AND a.location in (&&TEST_VAR).
with the date in input file StartDate is 01-Jul-2018.
The action flow is :
Oh, yeah…hmm, I don’t know if we ever thought of nesting file iterators like that. It looks like we are having some problem internally when making the substitution.
At first I got the same error as you, but then I changed my File Iterators so that they are in the same order as the variables in the query and it worked after that.
So try making File Iterator1 the one with the StartDate.txt file and File Iterator2 the one with your testfile.txt
And, not related to the problem, but you should always use to_date to avoid conversion problems. I would change
BETWEEN to_date(&&StartDate,‘DD-MON-YYYY’)) AND ‘31-Jul-2018’ and
to
BETWEEN to_date(&&StartDate,‘DD-MON-YYYY’)) AND to_date(‘31-JUL-2018’, ‘DD-MON-YYYY’) and