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     MySQL JOIN on multiple columns

Read-Only Read-Only Topic
Go
Search
Notify
Tools
MySQL JOIN on multiple columns
 Login/Join
 
migraine
Member
posted
Hello. When I execute this code

******************************************
JOIN
LEFT_OUTER SOME_VIEW.PE AND SOME_VIEW.FISCAL_YEAR IN SOME_VIEW TO MULTIPLE
PE_LINKS.PE_NUMBER AND PE_LINKS.FY IN PE_LINKS TAG J0 AS J0
END

TABLE FILE SOME_VIEW
PRINT
SOME_VIEW.PE
SOME_VIEW.FISCAL_YEAR
SOME_VIEW.FUNDING_AMT
J0.PE_LINKS.PE_LINK
WHERE SOME_VIEW.RPID EQ 52127;
ON TABLE PCHOLD FORMAT HTML
END
******************************************

I get the following error:

(FOC1400) SQLCODE IS 12547 (HEX: 00003103)
(FOC1394) CONNECT FAILURE
: ORA-12547: TNS:lost contact
L (FOC1406) SQL OPEN CURSOR ERROR.

I notice when I remove the field J0.PE_LINKS.PE_LINK from the request the report executes fine.

Anyone have experience with this error? The backend database is MySQL. I'm wondering if there is a setting the is not turned on to allow for this type of JOIN.

Thanks,
Matt
 
Posts: 14 | Registered: April 27, 2012Report This Post
Francis Mariani
Expert
posted Hide Post
Try it without the TAG clause. Also, for testing purposes, have you tried specifying one of the Joined table columns, eg. PE_LINKS.FY instead of PE_LINKS.PE_LINK? Is PE_LINKS in a different database?


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
migraine
Member
posted Hide Post
Thanks for the response.

Wow. PE_LINKS is the one table we are accessing from an Oracle database. Completely slipped my mind until you asked that. You nailed it. So it seems it limits me in how I join these 2 tables? I tested my logic out on my local SQL Server instance and it executed correctly and I ASSUMED (dumb me) that it would work in the client environment.

Bottom line I am having the darndest time executing a LEFT_OUTER JOIN that will grab, for example, 6 records from my view where the id equals 52127. Match those to the PE_Links table based on the Program_Element and Fiscal_Year. This should get about 4 records from PE_LINKS. Basically I want:

PENumber1234 FY2009 FUND$2300 http://ibi.com
PENumber1234 FY2010 FUND$4000 http://yahoo.com
PENumber1234 FY2011 FUND$5000 http://google.com
PENumber1234 FY2012 FUND$7200 http://cnn.com
PENumber1234 FY2013 FUND$3200 .
PENumber1234 FY2014 FUND$1000 .

Is this achievable going across different database types?

Again, thanks for you help thus far.
 
Posts: 14 | Registered: April 27, 2012Report 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     MySQL JOIN on multiple columns

Copyright © 1996-2020 Information Builders