DSXchange

Hello,

My source is Oracle,
And i have this columns in my source

FSCL yr,MTH_NUM,AMT

And my requirement is to calculate the YTD based on the month.

So for example if i am in the month num 2, then the YTD should be AMT in Month1 +AMT in month2

If i am in Month 3 then the YTD should be AMT in Month1 +AMT in Month2 +AMT in Month3 and so on,

This can be achieved by use of transformer stage variables:

Note: In the job, either run Sort and Transformer stages in Sequential mode or hash by FsclYr.

1. Sort incoming data by FsclYr, MthNum.

2. In transformer
2.1. Create following stage variables

• IF in.FsclYr <> svFsclYr THEN In.Amt ELSE (svYTDAmt + In.Amt) ===> svYTDAmt
  in.FsclYr ===> svFsclYr

2.2. Write incoming columns and svYTDAmt to output.

Thanks,
I checked that Logic, it works Good . Will this work for all the years and for all the line items.

Is there any way whether that can be accomplished in Oracle Source stage itself.

A Small correction to my Question


With this Data there is another additional field in the input i.e source_lin_ID,So the data is like this
Src_lin_id,FSCL_YR,MTH_NUM,AMT

1,1997,1,20
1,1997,2,30
.
.
.
2,1997,1,30
2,1997,2,40
.
.
.
.
3,1997,1,40
3,1997,2,50
.
.
.
.

So the key should be for the SRC_Lin_Id and year to calculate the YTD using month.


[My source is Oracle,
A.nd i have this columns in my source

FSCL yr,MTH_NUM,AMT

And my requirement is to calculate the YTD based on the month.

So for example if i am in the month num 2, then the YTD should be AMT in Month1 +AMT in month2

If i am in Month 3 then the YTD should be AMT in Month1 +AMT in Month2 +AMT in Month3 and so on.

I'd suggest you put something like "Oracle sql ytd" into your googler of choice and go through the various discussions online. You are not the first person to wonder how to do this in SQL.

Stage Variable Doesn't work for Different SRC_LIN_ID,chulet i have searched enough regarding this topic, but no valid answer

Hmmm... okay, if you say so. I found what seemed to be a number of valid answers, even if some of them were for SQL Server. At its core, SQL is SQL in spite of Oracle's sometimes unique take on the subject, so they can apply to Oracle as much as any other SQL-based solution.

You might want to explain what "stage variable doesn't work" means here. To me it looks like pretty standard group change detection logic and that can be done via stage variables rather easily. Now that previous answer may need to be fleshed out a bit to include all of the relevant columns but it can certainly be made to work for that purpose.