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] Multiple Conditional Joins does not work?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Multiple Conditional Joins does not work?
 Login/Join
 
Expert
posted
I was hoping to have over 10 Multiple Conditional Joins in a program, but just having two gives me this error:
 
(FOC1028) THIS TYPE OF RECURSIVE JOIN IN NOT SUPPORTED
(FOC1132) INSUFFICIENT STORAGE.  LN

All the files are flat files for the moment, until they get added to the database.

This is the fex:

JOIN LEFT_OUTER FILE R104H001 AT R104H001.CIS_2_QTR_AGO
TO UNIQUE FILE BSLRPT_TXT_CB_CI_SCR AT CIS2.CI_SCR_LABEL TAG CIS2 AS CIS2
WHERE R104H001.CIS_2_QTR_AGO GE CIS2.CI_SCR_LO AND R104H001.CIS_2_QTR_AGO LE CIS2.CI_SCR_UP;
END

JOIN LEFT_OUTER FILE R104H001 AT R104H001.CIS_1_QTR_AGO
TO UNIQUE FILE BSLRPT_TXT_CB_CI_SCR AT CIS1.CI_SCR_LABEL TAG CIS1 AS CIS1
WHERE R104H001.CIS_1_QTR_AGO GE CIS1.CI_SCR_LO AND R104H001.CIS_1_QTR_AGO LE CIS1.CI_SCR_UP;
END

JOIN LEFT_OUTER FILE R104H001 AT R104H001.CIS
TO UNIQUE FILE BSLRPT_TXT_CB_CI_SCR AT CI_SCR_LABEL TAG CIS AS CIS
WHERE R104H001.CIS GE CI_SCR_LO AND R104H001.CIS LE CI_SCR_UP;
END
-RUN

TABLE FILE R104H001
PRINT
R104H001.CIS_2_QTR_AGO
SEG.CIS2.CI_SCR_LABEL

R104H001.CIS_1_QTR_AGO
SEG.CIS1.CI_SCR_LABEL

R104H001.CIS
SEG.CIS.CI_SCR_LABEL

WHERE READLIMIT EQ 1000
END
-RUN

Commenting-out any two of the joins and related fields will make this work, having two joins and related fields will make this fail. I'm hoping I get confirmation that this happens ONLY because all these files are flat files, otherwise there's no point in me asking the DBA to add the files as DB2 tables.

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


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
Expert
posted Hide Post
quote:
The conditional join is supported for FOCUS and all relational data adapters.

I found this here:
http://documentation.informati...t/source/topic94.htm
Francis, I went to check this out because I was pretty sure that conditional joins didn't work against fixed files. Maybe you could do a quick load into FOCUS files to confirm that what you want to do is possible.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
Ginny!

Why didn't I think of that?!

I'll make them all FOCUS DB's and report back here.

Thanks for nudging my brain into thinking again!


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
Expert
posted Hide Post
It's the sleepy time of the day. I totally understand. I can't wait to hear the results.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
It works with FOCUS databases! I've tried it with three - now to expand to seven!

Thanks so much Ginny!

This message has been edited. Last edited by: Francis Mariani,


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
Expert
posted Hide Post
Seven Conditional Joins work!

Hallelujah, it's a miracle!


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
Virtuoso
posted Hide Post
By the nature of a join to a flat file, the flat file is read sequentially in parallel with the host, and the two need to be conformally sorted. Multiple joins to the same flat file, with or without the "conditional" syntax, wouldn't yield useful results.


- Jack Gross
WF through 8.1.05
 
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] Multiple Conditional Joins does not work?

Copyright © 1996-2020 Information Builders