Product: TIBCO Spotfire®
Teradata rejects Oracle-style join syntax in information links' auto-generated SQL
When trying to load data from a Teradata data source (using Teradata JDBC drivers) using a SQL query such as:
SELECT
s1."Name" AS "C",
R2."Name" AS "TEST"
FROM
"PROD"."daily" s1,
"VDATA"."RDIM" R2
WHERE
(s1."Party" = R2."Party"(+))
The following error is seen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Error message: Unable to import data. See the details for more information.
ImportException at Spotfire.Dxp.Data:
Failed to execute data source query. (HRESULT: 80131500)
Stack Trace:
at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
at Spotfire.Dxp.
<snip> ...
InformationModelException at Spotfire.Dxp.Data:
Failed to execute query: [Teradata Database] [TeraJDBC 12.00.00.114] [Error 3706] [SQLState 42000] Syntax error: expected something between '+' and ')'. (HRESULT: 80131500)
<snip> ...
InformationModelServiceException at Spotfire.Dxp.Services:
Failed to execute query: [Teradata Database] [TeraJDBC 12.00.00.114] [Error 3706] [SQLState 42000] Syntax error: expected something between '+' and ')'. (HRESULT: 80131509)
<snip> ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default generated SQL in a Spotfire information link uses Oracle syntax “(+)” to indicate joins. Not all database engines support this syntax.
Add this setting to <jdbc-type-settings> in the XML for your Teradata data source template in Spotfire:
<use-ansii-style-outer-join>true</use-ansii-style-outer-join>
If a setting that reads <use-ansii-style-outer-join>false</use-ansii-style-outer-join> already exists in the data source template, edit it to read as follows:
<use-ansii-style-outer-join>true</use-ansii-style-outer-join>
Background:
------------------
The table entry for use-ansii-style-outer-join in the table of available data-source-template XML settings in the Spotfire Server Installation and Configuration Manual notes that
----------------------------------------------------------------------------------------
'The default generated SQL uses the Oracle way with “(+)” to indicate joins. If this setting is set to true an attempt is made to rewrite it to standard ANSII format, making it possible to run on non-Oracle databases' that do not support this syntax.
----------------------------------------------------------------------------------------
The default value for this setting is False, but you can change a data source template to make it use ANSI style outer joins. After you have made this change and restarted the server, log into Spotfire Server from the Spotfire Professional desktop client once more. If the information link does not work now try creating a new, identical information link to see if the change takes effect then.