MySQL JOIN on multiple columns

May 02, 2012, 09:29 AM

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

May 02, 2012, 10:20 AM

Francis Mariani

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

May 02, 2012, 10:50 AM

migraine

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.