Hi, I have a table called incidentsm1 which has two fields that store ID's, opened.by and contact.name. I need to provide the full names related to the ID's. The problem I'm having is how to do two joins to the contctsm1 table where full name is stored. I'll try to explain with an example.

will have record in incidentsm1 where opened.by is ID01 and contact.name is ID02. I need to return the full name related to ID01 and ID02.

I two joins to the same table but report returned same fullname for both opened.by and contact.name when I know they are not the same, I also I tried a hold file. Any suggestions would be appreciated.

Two Joins to same table that didn't work

SET XRETRIEVAL=ON

JOIN
INCIDENTSM1.INCIDENTSM1.CONTACT_NAME IN INCIDENTSM1 TO MULTIPLE
CONTCTSM1.CONTCTSM1.CONTACT_NAME IN CONTCTSM1 TAG J1 AS J1
END
JOIN
INCIDENTSM1.INCIDENTSM1.OPENED_BY IN INCIDENTSM1 TO MULTIPLE
CONTCTSM1.CONTCTSM1.CONTACT_NAME IN CONTCTSM1 TAG J0 AS J0
END


Hold File Join that also didn't work

TABLE FILE CONTCTSM1
LIST CONTACT_NAME AND FULL_NAME
ON TABLE HOLD AS HOLDSM
END

JOIN
INCIDENTSM1.INCIDENTSM1.CONTACT_NAME IN INCIDENTSM1 TO UNIQUE
CONTCTSM1.CONTCTSM1.CONTACT_NAME IN CONTCTSM1 TAG J1 AS J1
END
JOIN
INCIDENTSM1.INCIDENTSM1.OPENED_BY IN INCIDENTSM1 TO UNIQUE
HOLDSM.HOLDSM.CONTACT_NAME IN HOLDSM TAG J2 AS J2
END

This message has been edited. Last edited by: <Kathryn Henning>,

JOIN
INCIDENTSM1.INCIDENTSM1.CONTACT_NAME IN INCIDENTSM1 TO MULTIPLE
CONTCTSM1.CONTCTSM1.CONTACT_NAME IN CONTCTSM1 TAG J1 AS J1
END
JOIN
INCIDENTSM1.INCIDENTSM1.OPENED_BY IN INCIDENTSM1 TO MULTIPLE
CONTCTSM1.CONTCTSM1.CONTACT_NAME IN CONTCTSM1 TAG J0 AS J0
END

Use the TAG prefix (the AS is to help reference the columns in subsequent TABLE requests - you need to have ASNAMES ON if you're creating a HOLD file with the AS option):

TABLE FILE INCIDENTSM1
PRINT
J1.FULL_NAME AS 'CONTACT_FULL_NAME'
J0.FULL_NAME AS 'OPENED_BY_FULL_NAME'
...

Thanks Francis, I'm not familiar with Tag names so I'm not sure what value I should be putting in Host Tag, Cross Tag and Join Name. I was getting the following error when running the join.

Error:
(FOC1831) TAGNAME OR FILENAME IS DUPLICATED IN THIS JOIN/COMBINE: @0000009


Join

SET ASNAMES=ON

TABLE FILE CONTCTSM1
LIST CONTACT_NAME AND FULL_NAME
ON TABLE HOLD AS HOLDSM
END
SET XRETRIEVAL=ON

JOIN
INCIDENTSM1.INCIDENTSM1.CONTACT_NAME IN INCIDENTSM1 TAG J1 TO MULTIPLE
CONTCTSM1.CONTCTSM1.CONTACT_NAME IN CONTCTSM1 TAG J2 AS J3
END
JOIN
INCIDENTSM1.INCIDENTSM1.OPENED_BY IN INCIDENTSM1 TAG J4 TO MULTIPLE
HOLDSM.HOLDSM.CONTACT_NAME IN HOLDSM TAG J5 AS J6
END

Francis,

I got the two joins to the same table to work. Thanks for pointing me i the right direction. I needed to use the fully qualified name.


TABLE FILE INCIDENTSM1
PRINT
INCIDENT_ID
J1.CONTCTSM1.FULL_NAME
J2.CONTCTSM1.FULL_NAME