Passing and Calulating Expression Dynamically

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
syedmuhammedmehdi
Participant
Posts: 43
Joined: Wed Feb 12, 2014 12:34 pm
Location: Hyderabad, India

Passing and Calulating Expression Dynamically

Post by syedmuhammedmehdi »

I'm getting following value of derivation in column flag_value from a table

if ((currentMonth <= 3 and ( ((mbrSubPlnAdjEndDt ="") or ((mbrEnrEndDtMonth >= 1) and (mbrEnrEndDtYear >= (currentYear - 1)))))) or (currentMonth > 3 and ( ((mbrSubPlnAdjEndDt ="") or ((mbrEnrEndDtMonth >= 7) and (mbrEnrEndDtYear >= currentYear)))))) then 'A' else 'I'

I'm calculating stage variables CurrentMonth, mbrSubPlnAdjEndDt and others in Stage Variable and above derivation I'm getting in other source column. This derivation is different for different Clients so it is going to be dynamically set. Here problem is when I'm passing this derivation in output column flag_value in derivation in transformer, it is taking it as text and not from stage variables calculated and not doing calculation of if/else. Could anyone please help how can we make transformer to take it as expression instead of text and do calculation for each record?
SyedMuhammadMehdi
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

The transformer stage doesn't support dynamic derivation expressions. The design-time derivation expression is compiled into the executable code.

If you have a reasonable number of clients that won't change often, you could handle the variable logic with a bunch of nested IF-THEN-ELSE.

If you want more flexibility, you will have to look at other design alternatives (e.g. custom routine).

Mike
syedmuhammedmehdi
Participant
Posts: 43
Joined: Wed Feb 12, 2014 12:34 pm
Location: Hyderabad, India

Post by syedmuhammedmehdi »

Hi Mike, thanks but please advise "bunch of nested if else" what do u mean?
SyedMuhammadMehdi
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

can you add a table to your database so you could look it up in whole or part? For example if each client has a static piece and the rest of the derivation is more consistent?
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

Bunch of nested if-then-else:

If Client = "A" Then Client A logic
Else If Client = "B" Then Client B logic
Else If Client = "C" Then Client C logic
...

Not at all flexible since you will have to change the job every time you add a client or a client needs a derivation change.

You'll have to go in another direction if flexibility is important to you.

Mike
syedmuhammedmehdi
Participant
Posts: 43
Joined: Wed Feb 12, 2014 12:34 pm
Location: Hyderabad, India

Post by syedmuhammedmehdi »

Yes, Thanks Mike, I'm creating Temp Table in DB with those variables as column and then doing custom derivation in SQL and it is working.
SyedMuhammadMehdi
Post Reply