Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator -- SQL Left Outer Join Only Allows Equijoins -- How Do I Cheat?
Go
New
Search
Notify
Tools
Reply
  
Data Migrator -- SQL Left Outer Join Only Allows Equijoins -- How Do I Cheat?
 Login/Join
 
Master
posted
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.



 
Posts: 916 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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?


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Master
posted Hide Post
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.



 
Posts: 916 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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,


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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,


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Master
posted Hide Post
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.



 
Posts: 916 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
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.



 
Posts: 916 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
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.



 
Posts: 916 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I'm glad you worked it out. Smiler


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator -- SQL Left Outer Join Only Allows Equijoins -- How Do I Cheat?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.