LookUp Stage Condition Specification Issue

hemaarvind1 · Post by **hemaarvind1** » Tue Aug 17, 2010 3:46 am

Hi All,

I have the following requirement:

I have a source A with a lookup B. Both the sources are Oracle databases.

I need to perform a lookup on these two sources based on key values.

The issue I have here is

The data coming from both the sources is similar,but differing in various aspects like case sensitivity,length of the strings.

For Example, Source A has data like "MOSCOW" and Source B has data like "Moscow ". Our requirement is to load this kind of data as a single string "MOSCOW".

I tried using lookup stage condition,however, I was not sure how to specify the condition for the above.

Could you please help me in this regard.

Thank You.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Aug 17, 2010 3:51 am

Maybe you are looking for UpCase() !?

hemaarvind1 · Post by **hemaarvind1** » Tue Aug 17, 2010 3:54 am

It is not Upcase. May be my query was a little vague to get you to that conclusion. Whatever data is coming in Source A , if it is present in Source B, I need data from Source A. There is no specific upcase requirement.

ETLJOB · Post by **ETLJOB** » Tue Aug 17, 2010 3:57 am

Convert data from the sources to Uppercase and do the lookup.

hemaarvind1 · Post by **hemaarvind1** » Tue Aug 17, 2010 4:01 am

Thank you for your suggestion. How about the strings that mismatch in length?

ETLJOB · Post by **ETLJOB** » Tue Aug 17, 2010 4:03 am

Did you try "trim" functions by any chance?

hemaarvind1 · Post by **hemaarvind1** » Tue Aug 17, 2010 4:09 am

I tried providing trim function,but as the condition is to be provided only on one input column , I could not compare for the other.

Like, For Source Table A and Lookup Table B, I can provide the trim condition on Lookup Stage for Table B. However, I can select only the columns of Source Table A for specifying condition,but I cannot specify condition for lookup table itself.

That is where I am getting stuck.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Aug 17, 2010 4:13 am

If both are in same db (and preferably same schema), try something like

Code: Select all

SELECT Trim(Upper(a.DETAILS)) yourColumn
FROM source1 a
UNION
SELECT Trim(Upper(b.DETAILS)) yourColumn
FROM source2 b

kris007 · Post by **kris007** » Tue Aug 17, 2010 4:14 am

You can always place a Transformer stage between the reference table and the LookUp stage and handle the trim in the Transformer stage.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 17, 2010 4:36 am

... or even a Modify stage.

kumar_s · Post by **kumar_s** » Tue Aug 17, 2010 8:51 am

Do a Inner join in the source with UPPER() and Trim function and then do a lookup in Datastage.
The first join in Database will ensure that, you get the Data from Source A only if it is present in Source B, and Vice versa. (You can change the join type if required.)