Amount Column as a key in Lookup File Set

[laknar]

Hi,

I have created AMOUNT as Key Column in Lookup file set(datatype Decimal).
And the same im using in next job.

But the match found(checked with query).But not coming in Lookupset expected result.

[kandyshandy]

What is your source stage?

[laknar]

Hi,

Input stage is DB2 API.

[gxhpainter]

You have to check the format of the data available in the Lookup set and format of the Data in source.

I would suggest, try to use some other column as key coulmn and write the Amount field to sequential file and source data to the same sequential file for testing purpose. Try to look for the format difference.

When you use SQL queries they usually match as they are in single world and single format, usually this would be the issue.

Rgds,
Chakradhar.

[kandyshandy]

Check how the data looks when you view data in source stage and in lookup fileset.

there is nothing wrong in using a decimal field as key. We just need to take care of the format DS uses to compare/join.

I assume that the length and scale are the same for that key field in both source and lookup fileset

[laknar]

Hi,

When i see the data from DB2 stage
12.900
From Lookupset
00000000000000000090.710

I believe due to this records not joined.

[Scope]

first thing use partition 'entire' for creating lookup fileset.

convert decimal to varchar in both db2stage and lookup fileset (use entire par.) before joining.

[laknar]

by default lookup fileset will use entire partition.

Converting decimal to string using TX Stage before joining.

Converting decimal to string before creating look up fileset .
Converting decimal to string from DB2 Stage before lookup(Using TX)

[kandyshandy]

Did you get this resolved?

[laknar]

i have convert the amount column as CHAR(Amount) in query.
but the records are not matching.

[kandyshandy]

Laknar, note a particular record and see how key looks like from source and look up file set.

[laknar]

i splited the records those which are joining and processed.
the records are getting joined.

e.g.
12 25.00 name
12 35.00 name1

when the above records process with other records the records not getting joined.it contains thousands of records.

e.g

code

12 25.00 name
15 34.00 name
15 44.00 name

[kandyshandy]

To put it simple... when the key matches, records will be joined. This is how DS look up works (based up on your join condition). So you may need to look by SQL how many records from source has matching keys in look up table, then see whether DS works the same way.