Avoiding Multiple Lookup

[ashishm]

Hi , I am running a job which requires me to do multiple lookups on the same table for each record.
I know in Informatica i could do an unconnected Lookup and call it for each column..Being new to DS environ still trying to figure out a better way.

The situation I am talking about is

ColumnA ColumnB ColumnC ColumnD ColumnE
1 A B C D


ANd Lookup Table is

ColumnA ColumnB
A XXX
B YYY
c ZZZ
D MMM

and the output I want is

1 XXX YYY ZZZ MMM

I dont want to do a Join as I may loose records using the JOin Stage.

Please suggest a better way to do it.

[chulett]

Does this need to be a PX job? There's an equivalent to the unconnected lookup on the Server side, hence the question. Not saying there isn't a PX answer to this, but nothing is popping into my head at the moment other than the Server solution.

Out of curiousity, what 8.x version do you have?

[ashishm]

I am using version 8.1 with fix pack 2.. and yes it has to be a PX JOb,Client does not wants too many Server Job..

[chulett]

How many is "too many"? Should be the right tool for the right job.

Was wondering if the looping functionality in the 8.5 release might be leveraged for something like this but it's academic, it would seem.

[ray.wurlod]

Use a Lookup stage with four reference inputs, all linking to the same reference data set.

[chulett]

Does that leverage a single cache under the covers, or four?

[ray.wurlod]

Four.

[prakashdasika]

I dont want to do a Join as I may loose records using the JOin Stage.

Please suggest a better way to do it.

Why do you lose records by using a Join Stage? Please Explain.

[chulett]

Four.

OK. That's what I thought... which means it doesn't really emulate the Informatica unconnected lookup but is akin to a 'connected' one, or rather four connected ones.