Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Duplicates in JOIN FIELD

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Duplicates in JOIN FIELD
 Login/Join
 
Member
posted
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,


WebFOCUS 8.x, Windows, HTML/PDF
 
Posts: 23 | Registered: April 29, 2016Report This Post
Virtuoso
posted Hide Post
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 !!!


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
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".


WebFOCUS 8.x, Windows, HTML/PDF
 
Posts: 23 | Registered: April 29, 2016Report This Post
Master
posted Hide Post
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.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
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.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Guru
posted Hide Post
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


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Duplicates in JOIN FIELD

Copyright © 1996-2020 Information Builders