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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Where not recognised in JOIN command

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Where not recognised in JOIN command
 Login/Join
 
bohorkez
Silver Member
posted
Hi guys, I'm making a conditional join

  
JOIN PRO_COD         IN   PROMOTION TO
     PDI_PRO         IN   PDISCONT AS J4
     WHERE (PDI_IPC  EQ 'L')
     WHERE (PRO_FFV  EQ PDI_FFV)
     WHERE (PRO_DATE EQ &WK_DATE);
END


My program executes normally but in that block of code, it's shown:
UNKNOWN FOCUS COMMAND  WHERE
 BYPASSING TO END OF COMMAND


The three first letter of the fields indicates the table they belong (PRO_COD belongs to PROMOTION - PDI_IPC belongs to PDISCONT)
I'm not getting an error, but I think that my join is not being executed the right way.

Do you know how I could resolve that?

Thank you so much.

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


WebFOCUS 7.1.4
Windows, All Outputs
 
Posts: 35 | Registered: December 21, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
Try a semicolon after each WHERE statement...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
bohorkez
Silver Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
Try a semicolon after each WHERE statement...


I tried that but the result is the same...


WebFOCUS 7.1.4
Windows, All Outputs
 
Posts: 35 | Registered: December 21, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
Try one WHERE at a time to determine which one might be the culprit.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
GamP
Virtuoso
posted Hide Post
Try reading up on the documentation concerning conditional joins. The example in the doc is:
JOIN FILE VIDEOTRK AT MOVIECODE TAG V1 TO ALL 
     FILE MOVIES   AT RELDATE   TAG M1 AS JW1
  WHERE DATEDIF(RELDATE, TRANSDATE,'Y') GT 10;
  WHERE V1.MOVIECODE EQ M1.MOVIECODE;
END
and the AT keywword is essential for conditional joins ... it is not in your join however ...


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Wep5622
Virtuoso
posted Hide Post
That's something that's been bugging me ever since I ran into the conditional join feature - why is the syntax totally different from the usual join syntax?

It does seem to contain the same information, a different syntax doesn't seem to make sense. It just seems to add a (superfluous) FILE keyword and turns the order of columns and tables around.

I suppose we could just start using this syntax for unconditional joins as well (just leave out the WHERE-clauses). Would that have any drawbacks? An obvious one comes to mind: the Report Editor, but I rarely use that anyway (I don't like the results much).


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
bohorkez
Silver Member
posted Hide Post
GamP is right, but I did not want to use the new syntax for two reasons:

1. All the programs of my installation don't use that syntax (JOIN FILE AT...) so I thought that maybe the conditional join is performed in a different way

2. Previously of my JOIN, I declared another conditional JOIN just using one WHERE (as Francis said) and I didn't get that messagge.

But I going to try the new structure of conditional JOIN and post what I get.

Thank you


WebFOCUS 7.1.4
Windows, All Outputs
 
Posts: 35 | Registered: December 21, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
I missed the AT issue.

AT - Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are simply used as segment references.

You must use the AT.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
bohorkez
Silver Member
posted Hide Post
As you guys suggested, I used this structure to perform my JOIN:
JOIN FILE PROMOTION AT PRO_COD  TAG V1  TO ALL
     FILE PDISCONT  AT PDI_PRO  TAG M1  AS J4
     WHERE (M1.PDI_IPC  EQ 'L');
     WHERE (V1.PRO_FFV  EQ M1.PDI_FFV);
     WHERE (V1.PRO_DATE EQ '&WK_DATE');
     WHERE (V1.PRC_PRO  EQ &WK_DEP_PRO);
END

Now I'm not getting the messagge.
Hope it helps someone with the same problem.

Thank you


WebFOCUS 7.1.4
Windows, All Outputs
 
Posts: 35 | Registered: December 21, 2011Report This Post
j.gross
Virtuoso
posted Hide Post
If a single WHERE is supported, just combine the several conditions into a single (equivalent) WHERE phase (as you would for SQL) --
   WHERE (PDI_IPC  EQ 'L')
   AND (PRO_FFV  EQ PDI_FFV)
   AND (PRO_DATE EQ &WK_DATE)
   ;
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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] Where not recognised in JOIN command

Copyright © 1996-2020 Information Builders