Left outer join in HASH file

karthi_gana · Posted: Wed Jul 21, 2010 4:09 am

All,

1) I have a sequential file. It contain 16000 records.
2) I have a HASH file. It contains 2000 records

I desiged the job like,

Code:

Seq_File _____> Transformer <--------- Hash File
|
|
|
v
Final_Seq_File

Seq_File contains Fund_Id, Portfolio_Id
Hash File contains Portfolio_id

Final_Seq_File contains 2000 records now.
But my requirement is, I have to store all 16000 records in the Final_Seq_File alongg with Portfolio_id (HASH FILE), if Portfolio_id is not matching with Seq_File, simply i have to store 'NULL'.

Sainath.Srinivasan · Posted: Wed Jul 21, 2010 4:30 am

Isn't that the default working mechanism - unless you have constrained something !!??

chulett · Posted: Wed Jul 21, 2010 5:43 am

Right, you have a constraint that only allows lookup successes to pass - you need to "turn that off".

karthi_gana · Posted: Wed Jul 21, 2010 7:16 am

chulett wrote:

Right, you have a constraint that only allows lookup successes to pass - you need to "turn that off".

I don't have any constraint under 'Transformer'.

Sainath.Srinivasan · Posted: Wed Jul 21, 2010 7:32 am

See the row count per link and find where the constraint is.

battaliou · Posted: Wed Jul 21, 2010 8:02 am

Er, you're trying to put NULL into a sequential file? Good luck.

chulett · Posted: Wed Jul 21, 2010 8:32 am

Have you checked your job's log to see if there were warnings logged during the write to the sequential file?

Sainath.Srinivasan · Posted: Wed Jul 21, 2010 9:38 am

Ensure that all columns are nullable. Or set a dummy value if the lookup fails.

karthi_gana · Posted: Thu Jul 22, 2010 12:28 am

chulett wrote:

Have you checked your job's log to see if there were warnings logged during the write to the sequential file?

Yes...I have checked the log...i din't see any warnings...

Sainath.Srinivasan · Posted: Thu Jul 22, 2010 1:57 am

Did you ensure that all NULL returns are made into a non-null values.

DataStage server is observed to be notorious in losing rows where NULLs are moved into non-nullable columns.

karthi_gana · Posted: Thu Jul 22, 2010 3:57 am

Sainath.Srinivasan wrote:

Did you ensure that all NULL returns are made into a non-null values.

DataStage server is observed to be notorious in losing rows where NULLs are moved into non-nullable columns.

I am using only two columns in the HASH file, both of them are NON NULL values.

so i choose 'NO' for Nullable property.

Sainath.Srinivasan · Posted: Thu Jul 22, 2010 4:06 am

They may be non-nullable in the hashed file. But if the lookup fails, the return is null.

karthi_gana · Posted: Thu Jul 22, 2010 4:15 am

karthi_gana wrote:

Sainath.Srinivasan wrote:

Did you ensure that all NULL returns are made into a non-null values.

DataStage server is observed to be notorious in losing rows where NULLs are moved into non-nullable columns.

I am using only two columns in the HASH file, both of them are NON NULL values.

so i choose 'NO' for Nullable property.

I am just thinking the below steps...

1) Get matching records
2) Get unmatching records
3) Merge both the files

i think it may be a workaround for this problem...

ray.wurlod · Posted: Thu Jul 22, 2010 4:34 am

Your steps 1 through 3 can be summarised as "get ALL the records".

arunpramanik · Posted: Thu Jul 22, 2010 5:15 am

karthi_gana wrote:

2) Get unmatching records

i think it may be a workaround for this problem...

That is what you are not getting in the job you have designed, so your work around will not work