Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

Join Question

robert fuschetto

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 3 months later...
  • 2 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...