Hi there,
I am trying to understand the following syntax since it's returning a 'DUPLICATES IN JOIN 'FROM' FIELD : TEK_HOLD_PPL_PROMO_DETAILS_FULL_LIST' error.

Here is the syntax.

quote:

JOIN CLEAR *
JOIN
LEFT_OUTER TEK_HOLD_PPL_HIRES_AND_PROMOS_OFFICE_LIST.TEK_HOLD.JOIN_KEY IN TEK_HOLD_PPL_HIRES_AND_PROMOS_OFFICE_LIST
TO MULTIPLE TEK_HOLD_PPL_PROMO_DETAILS_FULL_LIST.TEK_HOLD.JOIN_KEY IN TEK_HOLD_PPL_PROMO_DETAILS_FULL_LIST TAG J30 AS J30
END

This is an error from a Report Caster job. Thank you.solved]

This message has been edited. Last edited by: Tamra,

As far as I can understand the error it means that your

TEK_HOLD_PPL_HIRES_AND_PROMOS_OFFICE_LIST.TEK_HOLD.JOIN_KEY 

is not a unique key !!!

Thanks for the information. How can I implement a join in a 1 to many relationship?

TEK_HOLD_PPL_HIRES_AND_PROMOS_OFFICE_LIST contains the unique key while TEK_HOLD_PPL_PROMO_DETAILS_FULL_LIST contains multiple records with the same "key".

Here's a one-to-many example from a report I created:

JOIN INNER INCIDENT.INCIDENT.INCIDENTID IN INCIDENT
TO MULTIPLE TASK.TASK.PARENTID IN TASK TAG J001 AS J001
END

Table INCIDENT contains field INCIDENTID, which is a unique number.

Table TASK contains field PARENTID. There can be multiple records where PARENTID matches one INCIDENTID.

Your syntax looks ok for a 1-n join.

Judging from the message you encountered, it looks like your two "_LIST" files are flat files, and the TABLE request encountered duplicate JOIN_KEY values in the join-from file.

As j-gross mentioned, your syntax looks ok, it's your data that seems not.

In your parent file

TEK_HOLD_PPL_HIRES_AND_PROMOS_OFFICE_LIST.TEK_HOLD

the

JOIN_KEY

seems not to be unique which has to be.

How are you storing your data? is it in a non-focus relational database? If so then just declare it a unique join and make sure that the adapter is translating the sql properly. As long as its an RDBMS managed join and not a FOCUS managed join, your golden. Unique/Multiple only have real meaning when dealing with FOCUS data sources. These are not RDBMS concepts, but for WF will apply the same check logic either or. Unique has less of these issues.

Here is a link on the Techsupport site that relates to
Documentation on SQL SQLORA SET OPTIMIZATION SQL

Microsoft SQL Server > Optimization Settings

Depending on the type of RDBMS the above information will provide general concepts on working with non-FOCUS data.

Thank your for participating in the Focal Point Forum,
Tamra Colangelo
Focal Point Moderator
Information Builders