Looping Date values in Transformers
Moderators: chulett, rschirm, roy
Looping Date values in Transformers
Hi All,
I have a single record from source which contains minimum date - 2015/10/01 and maximum date - 2015/10/31.
Now, In target I want to generate in between dates using loop variables. So my target should contain 31 records including the min and max dates.
can some one help me on this.
Thanks
skumar
I have a single record from source which contains minimum date - 2015/10/01 and maximum date - 2015/10/31.
Now, In target I want to generate in between dates using loop variables. So my target should contain 31 records including the min and max dates.
can some one help me on this.
Thanks
skumar
You may leverage Oracle hierarchical functions thru Sparse Lookup for this.
STARTDATE and ENDDATE: Input date values in YYYYMMDD / VarChar format.
DateListVal: Output date list / Date format.
Code: Select all
SELECT (to_date(:STARTDATE,'YYYYMMDD') + (level - 1)) DateListVal FROM dual
CONNECT BY level <= (to_date(:ENDDATE,'YYYYMMDD') - to_date(:STARTDATE,'YYYYMMDD') + 1)
DateListVal: Output date list / Date format.
I tried using the loop variables with @ITERATION function. it worked well for me. here is the logic.
Stage variable1 - Min Date
Stage Variable2 -Max Date
Stage Variable3 - JulianDay(MaxDate)-JulianDay(MinDate)
Now, In loop constraint @ITERATION <=StageVariable3
In derivation of LoopVar - DateoffsetbyDays(Stagevariable1,@ITERATION-1)
I got the answer hence i am marking it as resolved. thanks all for your support
Thanks
skumar
Stage variable1 - Min Date
Stage Variable2 -Max Date
Stage Variable3 - JulianDay(MaxDate)-JulianDay(MinDate)
Now, In loop constraint @ITERATION <=StageVariable3
In derivation of LoopVar - DateoffsetbyDays(Stagevariable1,@ITERATION-1)
I got the answer hence i am marking it as resolved. thanks all for your support
Thanks
skumar
Skumar ... Cool.
And to answer previous questions ... Inspired by creative solutions and thinking outside of the box, that I have seen in this forum, like suggestions to leverage server job's capabilities when the OPs question was in parallel forum or advising them to use an Oracle literal when title mentioned bind variables![Wink ;-)](./images/smilies/icon_wink.gif)
Unless it is an interview question, developers are more interested in solving the issue, than strictly using a particular stage. In this case, preexisting code for similar req (date dim creation) was shared with OP.
And to answer previous questions ... Inspired by creative solutions and thinking outside of the box, that I have seen in this forum, like suggestions to leverage server job's capabilities when the OPs question was in parallel forum or advising them to use an Oracle literal when title mentioned bind variables
![Wink ;-)](./images/smilies/icon_wink.gif)
Unless it is an interview question, developers are more interested in solving the issue, than strictly using a particular stage. In this case, preexisting code for similar req (date dim creation) was shared with OP.