DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
syedmuhammedmehdi
Participant



Joined: 12 Feb 2014
Posts: 27
Location: Hyderabad, India
Points: 254

Post Posted: Wed Sep 13, 2017 7:14 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Unix
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



Group memberships:
Premium Members

Joined: 03 Mar 2002
Posts: 1010
Location: Omaha, NE
Points: 6513

Post Posted: Wed Sep 13, 2017 7:53 am Reply with quote    Back to top    

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
Rate this response:  
syedmuhammedmehdi
Participant



Joined: 12 Feb 2014
Posts: 27
Location: Hyderabad, India
Points: 254

Post Posted: Wed Sep 13, 2017 12:51 pm Reply with quote    Back to top    

Hi Mike, thanks but please advise "bunch of nested if else" what do u mean?

_________________
SyedMuhammadMehdi
Rate this response:  
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 252

Points: 2537

Post Posted: Wed Sep 13, 2017 2:08 pm Reply with quote    Back to top    

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?
Rate this response:  
Mike



Group memberships:
Premium Members

Joined: 03 Mar 2002
Posts: 1010
Location: Omaha, NE
Points: 6513

Post Posted: Wed Sep 13, 2017 3:27 pm Reply with quote    Back to top    

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
Rate this response:  
syedmuhammedmehdi
Participant



Joined: 12 Feb 2014
Posts: 27
Location: Hyderabad, India
Points: 254

Post Posted: Thu Sep 14, 2017 8:08 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours