Lookup on date fields.

meena · Post by **meena** » Fri Mar 23, 2007 1:01 pm

Hi All,
I am extracting the data from table (oracle) and creating a hash file.This hash file is used as a lookup in other job with the table(oracle). I have date fields in the hash file which are used as lookup.
The date fields of hash file and date fields of the table are in same format. But I do not know where I am doing wrong I am not able to compare them . Beside this, after running the job when I view the hash file the fields data are shifting.

Ex:

Hash file before run:

COL1,COL2,COL3
2006-09-28 00:00:00,2006-12-05 00:00:00,1

Hash file after run:

COL1,COL2,COL3
,2006-09-28 00:00:00,2006-12-05 00:00:00

This is for lookup on key COL1. If I am doing on the two(COL1,COL2) then the same with two blocks move.

I am not understanding why this is happening. I tried with the internal conversions of dates. But even I am facing the same problem.

Thanks in advance,
Meena

DSguru2B · Post by **DSguru2B** » Fri Mar 23, 2007 1:04 pm

Make sure you have identical metadata both, while building the hashed file and while referencing it.
Also make sure that you trim the key fields while loading the data and also during lookup, trim the source keys.

meena · Post by **meena** » Fri Mar 23, 2007 1:48 pm

Hi DSguru2B,
I did use trim and also tried it with internal conversion. But I am getting same problem of fields data moved to next field(hash file after job run) only for date lookup fields.

Thanks,
Meena.

chulett · Post by **chulett** » Fri Mar 23, 2007 2:07 pm

Fields 'move' in a hashed file when your metadata isn't identical across uses. Meaning, if you create the hashed file with two keys and three data fields then your lookup will need the same two keys and three data elements - and they must be declared in the same order.

Now, as with most rules, there are exceptions to this - but you really need to understand how things work under the covers before you go trying to break them. That being said, the typical reason for columns shifting is when Key fields aren't mentioned. Anything like that going on?

meena · Post by **meena** » Fri Mar 23, 2007 2:36 pm

Hi Craig,
Actually I was trying to redesign the job. Yes as you said this is because of the key not defined. One of the column used as the look up is not defined as a key in the first job. I have changed it and now everything is running fine.

Thanks a lot.
Meena

DSguru2B · Post by **DSguru2B** » Fri Mar 23, 2007 2:53 pm

As I indicated before, the metadata needs to be identical.