Problem while using LookUp stage and Join Stage

[dsuser_cai]

Hi

I have some problem when I use the LookUp Stage and Join stage. Following are the details:
Source Oracle 10, target: Oracle 10.
Im extracting data from 1 oracle table using an ODBC stage, then i use a look up stage to look up with another table (i use ODBCstage).

Source query:
select S.col1, S.col2 from source_table

Lookup query
select L.col1, L.col2 from look_up_table

im joining based on col1. data type is varchar2(10).

in target i need to take S.col1, S.col2, L.col2

when i apply this condition inthe look up stage i get a error

"Lookup_166,1: Failed a keylookup for record."

but i tried to write a query in oracle and i was able to pulle all the requied rows. I used trim function to trim the columns, still no go.

Then I tried to use a join stage. I used hash partition and sorted on the key column. Now i got a different out put.

Code: Select all

col1 col2 l.col2
1     v     100
2     u     0
3     w     101

in this row 1 and row3 are correct but row 2 should have a value and not 0. im able to get this if i run the query in oracle.,

i do not get any warning message apart from NLS warning message. Can anybody help me to fix this. right now im using oracle to query to insert into the table and that works fine, but i need to implement this in DS.

[Kryt0n]

Run it on a single node, that will ensure a partitioning issue isn't taking place.

If still no join then re-check your "2"... try outputing both to a flat file or do a hex dump of them. Constrain your two inputs to just row "2"

[ArndW]

The lookup stage should work correctly, regardless of the source data partitioning. The join does depend upon correct partitioning; you should partition on your join key to ensure that all rows are in the correct partition.

[Sreenivasulu]

Its better to use the option 'entire' in the lookup stage. This helps to have all the data looked up irrespective of the partition methodology

Regards
Sreeni

[HariK]

Remove the fail if lookup not found property and check if the result is same as the result of the join. then it most probably would be data problem.

[ray.wurlod]

Your "Lookup Failed" rule in the Lookup stage is probably set to its default value, which is "Fail" (= abort the job). Change the rule according to your requirements - there are three other possibilities.