The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
SQLException[Vertica][VJDBC](4856) ERROR: Syntax error at or near "10"
Hi,
Hope you are doing well.
I am new on vertical. While scheduling summarization jobs I am getting the error " SQLException[Vertica]VJDBC ERROR: Syntax error at or near "10" in JBoss.
Tagged:
0
Answers
You have an SQL statement that contains the literal
10
somewhere where it does not belong according to SQL syntax. Without knowing the SQL statement in question, knowing SQL, and finding out why the SQL statement became what it now is, you're stuck - and so are all of us. Can you share the SQL statement with us?Hi, marcothesane,
According to our observation, It is not a syntax error. It is a logical error. Need your help in finding a solution.
Best Regards
Maybe check the DC_ERRORS table? It contains a column called CURSOR_POSITION that can point you to where the syntax error is occurring.
https://www.vertica.com/blog/vertica-quick-tip-viewing-query-error-information/
Also, try running the SQL in vsql.
@Jim_Knicely also starts from the assumption of a syntax error. Only there do you get a
CURSOR_POSITION
. And SQLException only occurs when there is a SQL statement.It might be a logical error - in the JDBC client - JBoss it will be, even if I don't know that one, but it appears in the log snipped you shared. And the logical error in JBoss(?) has the undesired effect of generating syntactically wrong SQL code.
But, for Vertica, it's a SQL Syntax error. Try to unveil the SQL code you generate. In the worst of cases, query
query_requests
, the system table, for queries launched near or at the timestamp when you encountered the above error, and look (and share with us) therequest
column's content of that query.That is - find out the timestamp - and then:
, for example. Maybe you can use a narrower time slice than 15 minutes to find the culprit
Maybe the query won't show up in QUERY_REQUESTS?
Example:
I was able to view the SQL in the Vertica log:
Hi,
Thank you for your response.
Actually, we were testing on our lab and summarization jobs were working fine. Then we have uploaded all the batch files from production and removed the old ones after getting back up. One summarization job "FASocActiveAlarmsCountJob" has different SQL files for production and lab environments. which is producing "syntax error near 10".
Problematic SQL:
FAS_OCcountersExtractAndCalculate.sql
EXECUTE (10) select
'INSERT INTO TMP_OC_ACTIVE_ALARMS_COUNTERS (TIME, OPERATIONCONTEXTID, ALARMTYPEID, SEVERITYID, PROBABLECAUSEID, SPECIFICPROBLEMSID, MANAGEDOBJECTNAME, AOACKNOWLEDGED, AOOUTSTANDING, AOHANDLED, AONOTHANDLED) (WITH SNAPSHOTTIME AS (select TO_TIMESTAMP('''
|| TIME_SLICE(lastmodificationtimestamp,10,'MINUTE') ||
''',''YYYY-MM-DD HH24:MI:SS'') AS SELECTEDTIME)
SELECT SNAPSHOTTIME.SELECTEDTIME "TIME", OPERATIONCONTEXTID, ALARMTYPEID, SEVERITYID, PROBABLECAUSEID, SPECIFICPROBLEMSID, MANAGEDOBJECTNAME,
sum(case when ACKFLAG = 1 and ACKTIMESTAMP < SNAPSHOTTIME.SELECTEDTIME AND (TERMFLAG = 0 or (TERMFLAG = 1 AND TERMTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) then 1 else 0 end) AOACKNOWLEDGED,
sum(case when (SNAPSHOTTIME.SELECTEDTIME > CREATIONTIMESTAMP) and (ACKFLAG = 0 or (ACKFLAG = 1 AND ACKTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) and (TERMFLAG = 0 or (TERMFLAG = 1 AND TERMTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) then 1 else 0 end) AOOUTSTANDING,
sum(case when HANDLEFLAG = 1 and HANDLETIMESTAMP < SNAPSHOTTIME.SELECTEDTIME AND (CLOSEFLAG = 0 or(CLOSEFLAG = 1 AND CLOSETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) then 1 else 0 end) AOHANDLED,
sum(case when (SNAPSHOTTIME.SELECTEDTIME > CREATIONTIMESTAMP) and (HANDLEFLAG = 0 or(HANDLEFLAG = 1 AND HANDLETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) and (CLOSEFLAG = 0 or (CLOSEFLAG = 1 AND CLOSETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) then 1 else 0 end) AONOTHANDLED
FROM FCT_FAULT, SNAPSHOTTIME WHERE ALARMCLASS = 0 and
(
(ACKFLAG = 1 and ACKTIMESTAMP < SNAPSHOTTIME.SELECTEDTIME AND (TERMFLAG = 0 or (TERMFLAG = 1 AND TERMTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME))) or
((SNAPSHOTTIME.SELECTEDTIME > CREATIONTIMESTAMP) and (ACKFLAG = 0 or (ACKFLAG = 1 AND ACKTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) and (TERMFLAG = 0 or (TERMFLAG = 1 AND TERMTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME))) or
(HANDLEFLAG = 1 and HANDLETIMESTAMP < SNAPSHOTTIME.SELECTEDTIME AND (CLOSEFLAG = 0 or(CLOSEFLAG = 1 AND CLOSETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME))) or
((SNAPSHOTTIME.SELECTEDTIME > CREATIONTIMESTAMP) and (HANDLEFLAG = 0 or(HANDLEFLAG = 1 AND HANDLETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) and (CLOSEFLAG = 0 or (CLOSEFLAG = 1 AND CLOSETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)))
)
GROUP BY SNAPSHOTTIME.SELECTEDTIME, OPERATIONCONTEXTID, ALARMTYPEID, SEVERITYID, PROBABLECAUSEID, SPECIFICPROBLEMSID, MANAGEDOBJECTNAME
)
' from fct_fault WHERE ${data['CDCFILTER']} group by TIME_SLICE(lastmodificationtimestamp,10,'MINUTE')
Need help in solving the issue.
Best Regards