LookUp Stage Condition Specification Issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 50
- Joined: Mon Jan 21, 2008 9:35 am
LookUp Stage Condition Specification Issue
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 50
- Joined: Mon Jan 21, 2008 9:35 am
-
- Participant
- Posts: 50
- Joined: Mon Jan 21, 2008 9:35 am
-
- Participant
- Posts: 50
- Joined: Mon Jan 21, 2008 9:35 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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.)
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.)
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'