less rows from lookup

[DSRajesh]

Hi

I have source file and lookup file which are joined using trnasformer.
I am getting data for lookup from oracle table the problem i ma facing is
When i try to run the job for lookup individually using oracle and hash file for lookup i am getting exact rows as in database even after applying where clause.

But when i joined this lookup with source i ma getting less rows from lookup which was not like that when the lookup individually.

can u pls let me know why am i getting less number of rows after join with source where as i am getting same number of rows when ru the job individually for lookup.

pls help me here

[ArndW]

I am not sure where the hashed file fits in. But which lookup is not working and can you identify a row which should be found in the lookup but is not?

[ray.wurlod]

What is the data type of the lookup (reference) key? Are you certain that you trimmed trailing space when loading the hashed file and when you form the reference key value?

[DSRajesh]

Hi

I have the column flieght_sched_key in my table and hash file with value
2405269
but i am unable to find the key value for 2405265 in hash file where as same exist in my table.

Column data type is integer and i am not doing any trimming on this column.

can you pls help me why i am uanble be view the rwo with key 2405265 in hash file.

[Raghavendra]

Please first of all check whether you are using correct columns for the lookup then check the datatypes both from table and lookup.

[DSRajesh]

I am using correct columns only
metadata is same in table as well as hash file

can you pls advice me why this is happening

[hemachandra.m]

When it is 2405269 in table how will you find 2405265 in hash file. 2405269 <> 24052695. Check if the lookup has 2405269.

[hemachandra.m]

If still the problem persists. Just trim the key column in the hash file. Also while joining with hash file from source file have a trim of the source field.

[Raghavendra]

try once trimming both the columns

[DSRajesh]

how can we trim the column in hash file?
becuase the job design which is in production cannot have any stage like transformer between sourec table and hash file as it is direct mapping

can u pls advive me how do we do in hash file

[Raghavendra]

You cannot trim the columns directly in hashfile.If you don't have any transformer between the hashfile and the source stage try trimming the column directly at the source only( I mean database only.). But please be cautious while doing this as it may impact your job performance.

[Raghavendra]

Please post the server issues in server forum only.... :D

[hemachandra.m]

I had a similar problem where my hash file had 3 keys in which the key cols were not continuous from the start. So if a transformer is not used when data is transfomed to hash file then it would give problem if the keys are not continuous from the start. So try giving the key column as the first column in the hash file. I believe this should solve the issue.

[DSRajesh]

Hi hema,

I had totally 4 coulmns in my table where first 3 columns are hey columns and the last column which is not key column is missing the data in hash file where the row exists in database .

tried in many ways but in vain.

pls help me here

[DSRajesh]

also i am able to see the data when test with sequential files