Toad World® Forums

Error: You have an error in your SQL syntax;' when contacting View from Tableau


#1

When using a View in TIC as datasource in a Tableau workbook I constantly get the error that:

Error: You 'have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)) AND (SGBSTDN.SGBSTDN_MAJR_CODE_1 = STVMAJR1.STVMAJR_CODE(+))’ at line 84

This seems to suggest that the MySQL server has trouble with the ANSI code for left outer joins (it’s the ‘+’ of the outer join syntax that the error indicates. Removing the ‘(+)’ from the SQL simple shifts the error to the next ‘(+)’ in that statement.

Meanwhile the dataset in the Tableau workbook seems to perform as normal.


#2

Tableau considers TIC as a MySQL data source, so when we query a table/view/dataset from TIC we need to use MySQL syntax instead of others(Oracle, SQL SERVER…) especially other data sources’ dialect. Or else Tableau will throw errors when it checks SQL syntax, right?

So if you want to do some JOIN operation, I suggest you to use LEFT OUTER JOIN or RIGHT OUTER JOIN to replace ‘(+)’ in your SQL.


#3

Hi ArjenLissenberg,

How many data sources in your sql query? TIC support normal single data source query and multiple data source cross-query, we want to know your case belong to which type.

What’s your data source for this view/sql ? Oracle ?

Can you provide a completed sample sql for us checking?


#4

I might try that, is there an easy way to transform (also considering it has a subquery) in Toad?


#5

Just one datasource in my query: Oracle, it does have a subquery:

SELECT DISTINCT
SYVNAME.SID,
SPBPERS.SPBPERS_SEX,
SGRSATT.SGRSATT_ATTS_CODE,
MAX (SGBSTDN.SGBSTDN_MAJR_CODE_1)
AS MAX_MAJR_CODE_1,
MAX (STVMAJR1.STVMAJR_DESC)
AS MAX_STVMAJR1_DESC,
SGBSTDN.SGBSTDN_TERM_CODE_EFF,
SFBETRM.SFBETRM_ESTS_CODE,
MAX (SGBSTDN.SGBSTDN_MAJR_CODE_1_2)
AS MAX_MAJR_CODE_1_2,
MAX (STVMAJR2.STVMAJR_DESC)
AS MAX_STVMAJR2_DESC,
CASE
WHEN STVMAJR2.STVMAJR_DESC IS NULL THEN STVMAJR1.STVMAJR_DESC
ELSE STVMAJR1.STVMAJR_DESC || ‘/’ || STVMAJR2.STVMAJR_DESC
END
AS FINAL_MAJOR,
SUBSTR (SGRSATT.SGRSATT_ATTS_CODE, 3, 2)
AS CLASS,
‘Class of 20’ || SUBSTR (SGRSATT.SGRSATT_ATTS_CODE, 3, 2)
AS CLASS_OF,
MAX (SGBSTDN.SGBSTDN_DEGC_CODE_1)
AS MAX_SGBSTDN_DEGC_CODE_1,
SYTRESD.STVRESD_2CHAR_CODE,
STVESTS.STVESTS_DESC,
Subquery.PRV_TO_AST
FROM SATURN.SGBSTDN SGBSTDN,
SATURN.STVCAPL STVCAPL,
SATURN.STVMAJR STVMAJR1,
SATURN.STVMAJR STVMAJR2,
SATURN.SFBETRM SFBETRM,
SATURN.SGRSATT SGRSATT,
SATURN.SYTRESD SYTRESD,
GENYALE.SYVNAME SYVNAME,
SATURN.SHRDGMR SHRDGMR,
SATURN.SPBPERS SPBPERS,
SATURN.STVESTS STVESTS,
(SELECT CASE
WHEN SPRCMNT_1.SPRCMNT_CMTT_CODE = ‘PRV’ THEN ‘*’
ELSE ‘’
END
AS PRV_TO_AST,
SPRCMNT_1.SPRCMNT_PIDM
FROM SATURN.SPRCMNT SPRCMNT_1
WHERE (SPRCMNT_1.SPRCMNT_CMTT_CODE = ‘PRV’)) Subquery
WHERE (SGRSATT.SGRSATT_ATTS_CODE LIKE ‘YC%’)
AND (SGBSTDN.SGBSTDN_TERM_CODE_EFF = :Term)
AND (SFBETRM.SFBETRM_ESTS_CODE IN (‘AB’,
‘AT’,
‘EL’,
‘FC’,
‘HT’,
‘LA’,
‘LT’))
AND (CASE
WHEN STVMAJR2.STVMAJR_DESC IS NULL THEN STVMAJR1.STVMAJR_DESC
ELSE STVMAJR1.STVMAJR_DESC || ‘/’ || STVMAJR2.STVMAJR_DESC
END LIKE
‘%’ || :Major || ‘%’)
AND (SGBSTDN.SGBSTDN_STST_CODE = ‘AS’)
AND (SGBSTDN.SGBSTDN_LEVL_CODE = ‘UG’)
AND (SPBPERS.SPBPERS_DEAD_IND IS NULL)
AND (SGBSTDN.SGBSTDN_CAPL_CODE = STVCAPL.STVCAPL_CODE(+))
AND (SGBSTDN.SGBSTDN_MAJR_CODE_1 = STVMAJR1.STVMAJR_CODE(+))
AND (SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFBETRM.SFBETRM_TERM_CODE)
AND (SGBSTDN.SGBSTDN_PIDM = SFBETRM.SFBETRM_PIDM)
AND (SFBETRM.SFBETRM_ESTS_CODE = STVESTS.STVESTS_CODE)
AND (SFBETRM.SFBETRM_PIDM = SHRDGMR.SHRDGMR_PIDM)
AND (SFBETRM.SFBETRM_PIDM = SPBPERS.SPBPERS_PIDM)
AND (SGBSTDN.SGBSTDN_PIDM = SYVNAME.PIDM)
AND (SGBSTDN.SGBSTDN_RESD_CODE = SYTRESD.STVRESD_CODE(+))
AND (SGBSTDN.SGBSTDN_PIDM = SGRSATT.SGRSATT_PIDM)
AND (SGBSTDN.SGBSTDN_TERM_CODE_EFF = SGRSATT.SGRSATT_TERM_CODE_EFF)
AND (SGBSTDN.SGBSTDN_MAJR_CODE_1_2 = STVMAJR2.STVMAJR_CODE(+))
AND (SFBETRM.SFBETRM_PIDM = Subquery.SPRCMNT_PIDM(+))
GROUP BY SGBSTDN.SGBSTDN_MAJR_CODE_1,
SGBSTDN.SGBSTDN_MAJR_CODE_1_2,
CASE
WHEN STVMAJR2.STVMAJR_DESC IS NULL THEN STVMAJR1.STVMAJR_DESC
ELSE STVMAJR1.STVMAJR_DESC || ‘/’ || STVMAJR2.STVMAJR_DESC
END,
SYVNAME.SID,
SPBPERS.SPBPERS_SEX,
SGRSATT.SGRSATT_ATTS_CODE,
SGBSTDN.SGBSTDN_TERM_CODE_EFF,
SFBETRM.SFBETRM_ESTS_CODE,
SGBSTDN.SGBSTDN_DEGC_CODE_1,
SYTRESD.STVRESD_2CHAR_CODE,
SUBSTR (SGRSATT.SGRSATT_ATTS_CODE, 3, 2),
STVESTS.STVESTS_DESC,
STVMAJR1.STVMAJR_DESC,
Subquery.PRV_TO_AST,
‘Class of 20’ || SUBSTR (SGRSATT.SGRSATT_ATTS_CODE, 3, 2)


#6

From Main Menu, select ‘Query Builder’ and you’ll see ‘ANSI Joins’. Select that to switch join type


#7

We are checking your sql, will give feedback asap when we get the result.


#8

ArjenLissenberg,

We found this issue should be caused by below sql, seems something wrong when TIC parse below sql, as a workaround, can you just remove the LIKE ‘%’ || :Major || '% in sql and try again?

END LIKE
’%’ || :Major || '%

And we also create a Jira item (TIC-8866) to follow up this issue, development team would fix it in the coming release.


#9

In oracle “||” is that string concatenation, but in mysql means a logical OR. In the optimization process, wrongly parse it as a logical OR. There is a temporary way to avoid the wrongly parse, you can enclose the operation of the string concatenation in brackets. We will solve this problem.