Problems with STAGE VARIABLE DERIVATION

[kumarjit]

I am having a source table named PRODUCT .
Its schema is given below.

Code: Select all

PROD_ID            VARCHAR2(11 CHAR)
NAME               VARCHAR2(50 CHAR)
PRICE              VARCHAR2(5 CHAR)
GROUP_CODE         VARCHAR2(4 CHAR)

Examples of data in column PRICE are 80, 70, 170, 90, 70

My intention is to use the looping feature of tranformer stage and create a sub total of the column PRICE for each DISTINCT value of GROUP_CODE column.

I created two stage variables and used the following experssions for them :

PRICESUM=If LastRowInGroup(DSLink4.GROUP_CODE) Then 0 Else PRICESUM + DSLink4.PRICE
PRICETOTAL=If LastRowInGroup(DSLink4.GROUP_CODE) Then PRICESUM + DSLink4.PRICE Else 0

The datatypes for the above stage variables are Varchar.

The problem I am facing is that the values of these stage variables are not getting evaluated properly.


Can anyone please help me on this ?

Thanks
Kumarjit.

[zulfi123786]

Arithmetic operations on varchars is not right approach. convert them into integers using AsInteger() and even before that, check if they can be converted into integers using num() .

[BI-RMA]

Hi Kumarjit,

ad 1: you are not using the looping-feature of the transformer stage, but the key-break-detection feature, which is not the same.

ad2: datatype Varchar for the stage variables is false. DataStage can't do arithmetic operations using Varchar. Use decimal-datatype and correct conversion-functions.

ad3: you initialize PRICESUM with 0 on LastRowInGroup and then you calculate PRICETOTAL as PRICESUM + DSLink4.PRICE. This is wrong.
You need to change order of execution and initialize PRICESUM with 0 on the outset.

PRICETOTAL = PRICESUM + DSLink4.PRICE
PRICESUM: If LastRowInGroup(DSLink4.GROUP_CODE) Then 0 Else PRICETOTAL

The LastRowInGroup-function on PRICETOTAL can be omitted.
Instead you should use the function on the transformers link-constraint, so that only the last row per group is output to the link.

[FranklinE]

Perhaps I'm missing something, but why not use Aggregator stage? With Zulfi's warning about converting to numbers, Aggregator will do what you need.