I have two consecutive left outer joins where I need to break a multiplicative effect. I can preprocess the **** out of my data, but that would prove to be remarkably damaging to my frail masculine ego. Here's the nutshell:

1. I have multiple joins tacked on in a row to a table called encounter. One of the joins is to a table called "Race" where each record in the encounter can have zero to many race values.

2. I have another table coming in after that called TestResult where each record in the encounter can have one to many test result values.

I want my output (XML adapter) to show two races and two test results, but currently shows two races and four test results, two copies of each due to the multiplicative effect on two race entries. I need a left outer join to get all records regardless of the numbers of each, so I need a way to attach the sets that uses an "equijoin" since Data Migrator whines at me when I try to do anything else. Trying to only attach when the test result ID is '1' or other painfully obvious solutions fail due to this equijoin requirement.

Has anyone walked down this road, and if so, how did you solve the problem?

J.

John, would this be an accurate example of what you're seeing now?

race test result
1 A
1 A
2 B
2 B

and is this what you want to see?

1 A
2 B

If so, can you put a MAX function on the test result field? If this is not a good example, can you provide one? And can you provide the release of Data Migrator and operating system you're on?

Actually, what I'm getting is this --

1 A
1 B
2 A
2 B

What I want is a bit more curious to describe. Since this is going to an xml output file, I want

1
2
A
B

That is, I want each output record to have two hierarchical structures, one after the other (they're not nested) where the first shows the races selected and the second shows the test results. Multiple records with the same key result in multiple entries in a single record in xml, but it appears that the rule is only applying to the first level. For this second level I'm getting a multiplicative.

Thank you VERY much for replying. The I-Way forums can get quite lonely.

J.

Another question: if you were going to output this to a text file instead, would the unique values of race and test result look like this:

1,A
1,B
2,A
2,B

I'm trying to make sure I understand what your source data looks like.

This message has been edited. Last edited by: Jessica Bottone,

And can you post the SQL from your data flow and the synonym for your target?

This message has been edited. Last edited by: Jessica Bottone,

I can indeed include it.

SELECT
T1.ENCOUNTER_ID ,
T1.SITE_NUMBER ,
T1.ENCOUNTER_DATE ,
. . . many dozens of fields . . .
T3.RACE ,
T4.TEST_ID ,
T4.TEST_SITE_NUMBER ,
. . . fields fields fields . . .
T3.FOCLIST AS RACEID ,
T1.DATA_SOURCE ,
T2.AGENCY_ID ,
T1.ENCOUNTER_DATE_SMART
FROM
(((enc_plus T1 LEFT OUTER JOIN site_type_view T2
ON
T1.SITE_NUMBER = T2.SITE_NUMBER_FIXED ) LEFT OUTER JOIN race_tab T3
ON
T1.ENCOUNTER_ID = T3.ENCOUNTER_ID ) LEFT OUTER JOIN testresult T4
ON
T1.ENCOUNTER_ID = T4.ENCOUNTER_ID )
WHERE
ENCOUNTER_DATE_SMART >='&STARTDAY' AND
ENCOUNTER_DATE_SMART <='&ENDDAY' AND
(T1.ENCOUNTER_ID = 'PC00093462' OR T1.ENCOUNTER_ID = 'PC00118201' OR T1.ENCOUNTER_ID = 'PC00121278' OR T1.ENCOUNTER_ID = 'DC10012199' OR T1.ENCOUNTER_ID = 'DC10011323' OR T1.ENCOUNTER_ID = 'DC10011756' )
ORDER BY
T1.ENCOUNTER_DATE
END

The WHERE clause on the explicit Encounter_ID fields is to zoom in on troublesome records and has no effect on the behavior, as do the date clauses.

Regarding your earlier question, I think this output to a text file --

1,A
1,B
2

. . . would provide the correct xml output. The xml adapter simply makes a list of all the resulting values, and just lines them up. That's exactly what I'm looking for, so I don't think that's an issue.

In theory this is easy -- a left outer join where you only pull test results where race=1 or race=null. But, in order to do that I would need to specify those two conditions in the join logic, and Data Migrator is telling me that's verboten. Equijoins only.

I think I got it.

I need an equijoin, and I need a single field equijoin at that. So I created a field in the Test Results table that concats the Encounter part of its key to the Test Result part of its key.

Then I created a field in the Races table that concats the Encounter part of its key to the static value '01'.

Then I perform my equijoin based upon those two concatenated fields. The Race records attach themselves to the first Test Result record and no others.

I got a bit of a break here -- there is a minimum requirement of one Test Result, so I can depend upon that 01 value appearing in the Test Result defined field for each Encounter. At that point all the Race records tie to the first Test Result record, and I indeed get exactly what I want in my xml output. In the event there are no Race records, the left-outer join allows the rest of the material to proceed through to the final destination.

My frail masculine ego remains intact. Thanks for the help!

J.

I'm glad you worked it out.