AnsweredAssumed Answered

What is the best way to "split" a database document?

Question asked by TehNrd on Sep 26, 2011
Latest reply on Sep 27, 2011 by TehNrd
I know that database connection documents cannot be split but what is a sufficient work around?

I am querying two objects, a parent child relationship, and I am using the Link Element on the operation to group all of the parent records and their given child records as one document. These are then mapped to a parent child relationship.

Somewhere in this process I need to look at the individual child records within the linked document and conditionally map these to the target, yes or no. I know what you are thinking, why not just filter these items out in your original query? Yes, this is ideal but there are some complexities that do not make this easy. See below for more info.

So what is the best way to handle this? Is there any way in a map function to prevent a record from being mapped? Do I need to map to a flat file profile and then split this? Any other ideas?


More detail:
so why not just filter out the rows on the original connector operation? Maybe this can be done but I haven't been able to figure it out. Take this data set, assume today's date is 10/1/2011.

ID, Serial_Number, Expiration_Date
1, ABC, 10/15/2011
2, DEF, 10/15/2011
3, DEF, 12/31/2011

The query needs to return all records with an expiration date in the next 30 days but only if there is not another record with the same Serial_Number, sometime beyond the expiration date of the given row. So in this example data set the only row that should be returned 1. 3 should not returned as it is beyond the 30 day window, and row 2 while in the 30 day windows should not be returned at there is another record with the same serial number in the future, row 3.

If there is way to make this work with SQL that would be great but I haven't been able to figure it out.