What's the best method to write complex derivations?

[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

[chulett]

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.

[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.

[chulett]

I was assuming they meant "logic" errors more than actual syntax errors. Since they're, you know... complex.

[ray.wurlod]

Sometimes I answer the unasked question. Unknown unknowns, and all that...

[FranklinE]

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.

[asorrell]

UltraEdit Rules! Too bad it doesn't have the syntax checking for DataStage pre-built!

[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.

[asorrell]

Nice! Had no idea someone had done that.

[priyadarshikunal]

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).

[chulett]

Of course, just haven't really bothered yet. It's on my Rainy Day List, page 12.

[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.

[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.

[IASAQ]

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.

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?

[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.