DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
truenorth
Participant



Joined: 18 Jan 2010
Posts: 128
Location: San Antonio
Points: 861

Post Posted: Fri Sep 15, 2017 6:50 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
IA 11.3

On a join where a record in the left table matches to more than one record in the right table, I just need the first record, not all matches. There does not need to be a join. Say two customer records with the same customer ID…I only want to process/output the first one, not the others.

Unfortunately, IA’s dedupe only excludes duplicates based on the entire record.

Thoughts?

Thanks in advance.

_________________
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
JRodriguez



Group memberships:
Premium Members

Joined: 19 Nov 2005
Posts: 413
Location: New York City
Points: 4531

Post Posted: Sat Sep 16, 2017 1:39 pm Reply with quote    Back to top    

Hi Manito,
How about a virtual table to represent the right table with the desired records to return only one when joining with the left table? Of course you would need to use regular SQL techniques to get the table as you want...

Another way will be to do the entire join in a virtual table

_________________
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
Rate this response:  
Not yet rated
truenorth
Participant



Joined: 18 Jan 2010
Posts: 128
Location: San Antonio
Points: 861

Post Posted: Mon Sep 18, 2017 8:28 am Reply with quote    Back to top    

Buenas dias, manito! Glad to be able to touch base here.

JRodriguez wrote:
Hi Manito,
How about a virtual table to represent the right table with the desired records to return only one when joining with the left table? Of course you would need to use regular SQL techniques to get the table as you want...


Unfortunately, you're right...that table would have to be created outside of IA. I would have to involve other folks - DBA, IT infrastructure (they do IMAM, I don't). They do not have the bandwidth at the moment. I was hoping to use IA to pull this off.

JRodriguez wrote:
Another way will be to do the entire join in a virtual table


Only possible in 11.5, which is currently being installed and won't be vetted until yearend. We're still in 11.3 which only allows for very basic SQL for VTs.

Thanks for your insight though. Always helpful to pick your brilliant mind.

On another note, are you guys still using IA? Or did you stick with Datastage for DQ?

_________________
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours