Join Question

[robert fuschetto]

If I have two master files, one is based on an oracle detail table housing stats by DeptId(P16/ / I4) and the second on a lookup table - SQL server - holding DeptId (I11 / I4) and DeptName and some other attributes...and I want to JOIN the two. I know the DeptIds need to have the same actual and usage formats for a JOIN.

In this case they are not thesame so the GUI does not allow the JOIN. Let's say though that the Lookup table with the Primary Key DeptId (I11 / I4) has **ANOTHER** column: DeptId2 with the same values as DeptId but with actual and usage P16 / I 4.

I tried to JOIN to the lookup table's DeptId2. It let me since the actual and usage now matched. However the resulting Dept Name returned is INCORRECT.

If I instead change the actual/usage in the data table for DeptId to I11 / I4 and join to the lookup table's **PRIMARY KEY** key...all works fine.

Does this make sense? I would have thought it should work **EITHER** way but it seems if a primary key exists I must join only to it.

[David Beagan]

Based on what you describe, It seems that the join based on DeptId to DeptId2 would be expected to work. If the two fields are displayed side-by-side, DeptId (Oracle) and DeptId2, do you see all the values are matching?

[robert fuschetto]

First I made sure the lookup tables DeptId and DeptId2 always contained the same values...they do.

The errant join though always sees the data tables DeptId mathching to the look up tables DeptId2 value = 0 and it's description...no idea why. However if change the usage to I11/I4 and join the lookup tables primary key..it works fine. I opened a case.

[Debra Waybright]

@robert fuschetto Did you get this resolved? My initial thought is Join Multiple. I'm wondering if that is what the issue was or if it was something else. Always nice to see the solutions out here for others who might have a similar issue. 

[robert fuschetto]

Sorry, never really resolved.