Spotfire Ideas Portal
Free Hand Join: Right Outer Join Error in ( Tibco Spotfire), setting up a Data Model in information designer for users that don’t know SQL. I need
to be able to build more complex outer joins than just outer joining 2 tables using the default “left outer join” or “Right Outer Join”
I did research on line and the suggested syntax is { %1 *= %2 and %3 = 'Y' } however this results in an error.
Failed to execute query: [tibcosoftwareinc][SQLServer JDBC Driver][SQLServer]The query uses non-ANSI outer
join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is
strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes. (HRESULT: 80131509) I can manually write the SQL in ANSI syntax but this defeats the purpose of setting up the information Model in information designer for our analysts that don’t know SQL.
See case (Case_ID:01249143) for additional details. The following workarounds were recommended.
Filtering a table in a Outer Join before it is joined is something we have to frequently do in a relational database and once customers migrate to MS SQL 2012 and 2014 Non-ANSI joins will no longer be supported making the current Freehand join built into Information Designer effectively obsolete.
The proposed workarounds are noted below.
1)Create a Stored procedure for the join on the database that accepts parameters from the Spotfire information link
2)If you have more complicated joins involved with multiple target databases of different types (Oracle, SQL Server, and so on), then it might help to use Spotfire Advanced Data Services (ADS) to their Spotfire environment.