I am trying to create a join between two synonyms that each describe an external SQL query, on separate database connections. Both connections are MSSQL 2008 adapters. Report can be created from each synonym individually with no problems. However, a join attempt produces an FOC1406 SQL OPEN CURSOR ERROR.
Error is produced whether attempting the join in the report (via IA+) or creating the join in a clustered synonym.
If the synonyms are regular table descriptions (i.e. not from external SQL query), the join works correctly.
I have SUBQUERY=Y in the access file for each synonym. SQL Trace output confirms that Focus-managed join is enabled.
Is this scenario supported in BUE? Any help, info, or reference documentation would be appreciated.
February 15, 2017, 10:20 AM
The dynamic join will not work with external SQL synonyms. Try creating a report in IA+ using these steps:
Create a HOLD file for the join target file (e.g., external_SQL_2.mas) using the File option in the Format group / Home tab on the ribbon.
Create a Report off the HOLD file. The 1st thing you will do is create the Join using the Join button in the Join Group / Data tab on the ribbon. Click the Add New Data file button on the Join dialog. Add the join source file, (e.g., external_SQL_1.mas) and define your join. Click OK.
Finish creating your report/chart by adding fields. You should see fields from both synonyms.
Refer to the online help topic: Creating Content -> Creating Hold Files
Hope this helps. Regards, Richard SittThis message has been edited. Last edited by: Richard Sitt,
February 15, 2017, 03:21 PM
Thanks for the information, Richard. Which file format should the HOLD file be saved in?
WebFOCUS 8 Windows, All Outputs
February 15, 2017, 05:41 PM
Edward, Binary format is fine. As for location, put it in the application directory that matches your domain name. Regards, Richard