Using Lookup results to do another Lookup

chulett · Post by **chulett** » Thu Jan 08, 2004 9:52 am

Ok, this was interesting enough to me to share.

Let's say you have a Transform that needs to do three lookups and the third lookup has a key that is populated based on the results of one (or even both) of the two previous lookups. Normally, I would create a second transform, do the first two lookups in the first Transform, pass the results to the second Transform and do the final lookup there.

Even though you can't drag the results of a lookup to the key field of another, you can however type the field name in directly. And it works! Of course, you have to make sure things are happening in the correct link execution order. However, this just seems to fly in the face of convention and the fact that the GUI technically doesn't support this makes me wonder...

Any reason people know of why someone shouldn't be doing this?

kcbland · Post by **kcbland** » Thu Jan 08, 2004 10:02 am

Yeah, it's not supported and we don't know why. If it technically works, but Ascential engineering has disabled the GUI from allowing it, then there probably is something somewhere that doesn't work just right or reliable. I would guess you are seeing residual code from an attempt to make it work, but I wouldn't trust it.

To me, the graphical metaphor is sufficient reason to show the progressive reference. You also have removed a conditional reference based on a constraint restricting the row from proceeding to the subsequent transformer for the third reference.

In the age of crappy consultants underbidding experienced and knowledgable ones, we're going to have to be guaranteeing our work. This is a bomb I wouldn't leave a customer with, lest they came back after me.

--Sigh--, this is why I carry millions in professional and general liability insurance.

crouse · Post by **crouse** » Thu Jan 08, 2004 1:19 pm

This may start a religious debate, but...

Technically, the processing in the transformer (at least for Server side jobs) is procedural... top to bottom, left to right. Within the transformer, "anything you do before can be used after". Meaning, data enters the tranformer via the stream link, then all the reference links are processed in the order they appear on the left side of the transformer. Then, control is passed to the right side of the transformer. The right side processes the Stage Variables, in the order they appear from top to bottom, then the output links are processed from top to bottom. That is why you can use derived values, from links that appear above, in links that appear below.

Notice at the top of the transformer are two "link order" icons. This allows you to adjust the link order (i.e. the order data is processed within the transformer) of both the input and output links. Most people only use this feature to make sure the REJECT link is "at the bottom".

Now, that said, I will temper it with "all things are permissable, but not all things are good (at least not all the time)" I added the part in ().

Meaning, if you are aware of what is going on, by all means take full advantage of the product. And document it. DataStage lets you document just about anything and everything. If you're uncomfortable doing it a certain way, DataStage will undoubtedly offer another way to do it. Some are over, some covert... some easily maintained, others maybe not so easily maintained.

You are not "permitted" to simply "drag and drop" from prior links. I believe this is because it could lead to a false sense of security. Notice that "drag and drop" is permitted from left to right and from Stage Variables down, and the nice arrows are there to show the relationship. That is because Ascential engineers have control over input happening before output and stage variables happening before output links. That's it. If they permitted the dragging and dropping of columns from reference link to reference link or output link to output link, one starts to think they can drag from a bottom link to an upper link (which is very, very bad, again if you don't know what you are doing). So, Ascential engineers give you drag and drop ability and arrows for relationship on JUST the things they can control and guarantee.

Nothing stops you from typing in the link name and column name from any link to any other link. Just make sure you know what you are doing and what is happening.

Sorry for the pontification...

-Craig Rouse

kcbland · Post by **kcbland** » Thu Jan 08, 2004 2:22 pm

If Ascential wanted, they could automatically resort your reference link execution order during design time in the GUI to produce the right reference path if you do a cascading reference. Instead, they have opted to block the ability all together. Knowing how the product works, and guaranteeing how the product works are two different things.

Religious discussion time. If I design a job with a non-supported technique it is an exposure for failure during a future release or upgrade, as well as a potential landmine in the current release. Unless the functionality is critical, or catastrophic to the success of the work, it achieves little or no gain. Simply performing the reference in a subsequent transformer is vastly acceptable, especially for the previously stated reasons.

Taking advantage of a loophole or bug because you know the underlying framework is acceptable if you are paying the bills. If someone on my team decided to exploit a bug because of their laziness, their work would not pass QA and they would get to do it over, after a public lynching.

crouse · Post by **crouse** » Thu Jan 08, 2004 3:29 pm

Didn't mean to ruffle the feathers...

This capability has never been referred to as a bug by Ascential, but rather demonstrates the flexibility of DataStage. I've been working with DS since 3.6 (a newbie by some standards) and the capability has been there since at least then. Even Ascential would have fixed it by now if it were a bug.

And as far as constraint checking goes, all the results from the reference links can be checked in the appropriate output links.

Maybe an engineer could chime in and clear the air.

-Craig

kcbland · Post by **kcbland** » Thu Jan 08, 2004 4:40 pm

crouse wrote:Didn't mean to ruffle the feathers...

This capability has never been referred to as a bug by Ascential, but rather demonstrates the flexibility of DataStage.

Okay, loophole, undocumented/unsupported feature.

crouse wrote:Even Ascential would have fixed it by now if it were a bug.

Wanna bet?

crouse wrote:And as far as constraint checking goes, all the results from the reference links can be checked in the appropriate output links.

Yes, but you will incur all the overhead costs of all of the lookups. If you deferred a lookup to a subsequent transformer, you can reduce the number of mandatory reads/selects against a reference.

crouse wrote:Maybe an engineer could chime in and clear the air.

Would you settle for a former principal consultant?

ray.wurlod · Post by **ray.wurlod** » Thu Jan 08, 2004 4:53 pm

I'm with Ken on this one. Let's go one step further. There's nothing to stop me hacking the code produced by a Transformer stage, because I know how to get it compiled without recompiling the whole job. But would I do that, knowing that the next developer, lacking that technical knowhow, would recompile the job, and therefore regenerate the original Transformer stage code? That's right, no way! So where's the cutoff point? Using the tool "as advertised" is definitely the professional way to go; if you want this or that capability in the product, submit an enhancement request with appropriate business case as to why the change is desirable. (I note, in passing, that conditional lookups have been on the wish list for a number of years already, so don't hold your breath unless you've got a really good business case!)