Page 1 of 2
What's the best method to write complex derivations?
Posted: Thu Mar 17, 2016 12:33 am
by abyss
Hi,
I have a job that requires implementing complicated derivation logic in a couple of columns and a mapping table is not suitable for some reason. I am just wondering, what's the best method to write the code without too much error?
Is there any 3rd party derivation editor available? Something that can highlight the key words and variables?
thanks,
abyss
Re: what's the best method to write complex derivations?
Posted: Thu Mar 17, 2016 7:13 am
by chulett
abyss wrote:mapping table is not suitable for some reason
Not really sure what that means. "Mapping table"? Are you talking about rules driven derivations? Regardless, for Server or Sequence jobs it is easy to build a "test harness" - a custom BASIC routine with the derivation logic in it that you can test with various input values to ensure what comes out the end is correct. Unless you're attempting to integrate a rules engine in there somewhere.
As to your last question -
UltraEdit.
Posted: Thu Mar 17, 2016 3:58 pm
by ray.wurlod
The answer to this one has to be "it depends".
If you could indicate what kind of "complex derivation" you have in mind, then we could perhaps be more specific.
If there's one best practice for avoiding errors in expression construction, it would be to use the Expression Editor and choose all operators and operands (except numeric constants) from menus.
Posted: Thu Mar 17, 2016 5:42 pm
by chulett
I was assuming they meant "logic" errors more than actual syntax errors. Since they're, you know... complex.
Posted: Fri Mar 18, 2016 2:46 pm
by ray.wurlod
Sometimes I answer the unasked question. Unknown unknowns, and all that...
Posted: Tue Mar 22, 2016 8:36 am
by FranklinE
ray.wurlod wrote:Sometimes I answer the unasked question. Unknown unknowns, and all that...
Ray, I really don't know what you're talking about.
I was a big fan of Kedit in its heyday. Since I don't get to make purchasing decisions, I had to take SlickEdit when Kedit died a quiet death. It is clunky, but does everything I ask of it.
Posted: Tue Mar 22, 2016 9:53 am
by asorrell
UltraEdit Rules! Too bad it doesn't have the syntax checking for DataStage pre-built!
Posted: Tue Mar 22, 2016 9:57 am
by chulett
But it does, my friend - at least as a downloadable wordfile. Go
here and scroll down to DataStage BASIC. I used to tweak it to my tastes which from what I recall is very easily done. Now what they don't have is one for Informatica.
Posted: Tue Mar 22, 2016 10:25 am
by asorrell
Nice! Had no idea someone had done that.
Posted: Tue Mar 29, 2016 3:34 am
by priyadarshikunal
chulett wrote: Now what they don't have is one for Informatica.
You can have that created easily, just add keywords in C1, C2, C3 tags and you are done. Just need to make sure its sorted (or that may not be a requirement anymore).
Posted: Tue Mar 29, 2016 7:12 am
by chulett
Of course, just haven't really bothered yet. It's on my Rainy Day List, page 12.
Posted: Thu Jun 30, 2016 9:11 am
by IASAQ
Maybe I can surf on this thread as "complex derivation" had an exact match to what I was looking for.
My guess is that by complex derivation, the OP was thinking about when, for a single output field, you could have multiple lines of codes, including assigning variables to end up with a final value to be mapped to the output field.
Unless I'm mistaken, in a derivation expression, you can't assign work variables. From what I could gather from the short time I've been trying to work with Datastage, derivations seems more like this:
Code: Select all
if inputField = true then
"TRUE"
else
"FALSE"
than this:
Code: Select all
if inputField = true
tmpStr = "this is maybe my lucky string"
tmpInt = WordCount(tmpStr)
if (tmpInt) = 7 then
"You Win? Security!"
else
"Put more coins in the slot machine, sucker!"
else
"Marvin! Kick him out of my casino!"
After reading another thread related to this subject, looks like you'd have to use multiple stage variables to accomplish the goal instead of having everything in a single derivation expression. Or use a custom routine written in C++ or BASIC.
Posted: Thu Jun 30, 2016 5:12 pm
by ray.wurlod
No, complex expressions are OK in DataStage BASIC, but you have to know the rule that every multi-statement block has to be terminated with an END statement.
However, in a derivation expression things are different. You're right that you cannot assign work variables (use stage variables for these). A derivation expression is one that must produce a result. It is an expression that could be used on the right hand side of an assignment statement in a routine.
That said, your example could be done in a single expression with no stage variables. It would just be ugly.
Posted: Tue Jul 05, 2016 8:48 am
by IASAQ
ray.wurlod wrote:No, complex expressions are OK in DataStage BASIC
But routines written in BASIC are not available in parallel jobs, except with BASIC Transformer and we already discussed that I believe.
ray.wurlod also wrote:However, in a derivation expression things are different. You're right that you cannot assign work variables (use stage variables for these). A derivation expression is one that must produce a result. It is an expression that could be used on the right hand side of an assignment statement in a routine.
That said, your example could be done in a single expression with no stage variables. It would just be ugly.
So for the sake of comprehending correctly. a transformation that would require 5 manipulations for a single input field would require 5 stage variables if we wanted to be clean right?
Posted: Tue Jul 05, 2016 4:56 pm
by ray.wurlod
Expressions in parallel transformer stages are actually in BASIC. They are translated to C++ during "compilation". However, multi-statement blocks are not available in expressions; indeed no form of statement is available in an expression.
I continue to maintain that your example could be done in a single expression with no stage variables. But using stage variables would make it easier to understand and therefore more easily maintained.