All Forums Teradata Studio
Jkell1112 1 post Joined 09/12
18 Dec 2013
Unable to parse simple query: version 14.10.01

When doing a simple query that includes the Year() function:
Select  col1, YEAR(col2) from Table1; the following message appears and will not allow the query to run:
"Parse Error Splitting Statement"
Thanks for your help!

dnoeth 4628 posts Joined 11/04
18 Dec 2013

There's no YEAR function in Teradata or Standard SQL.
Depending on some ODBC settings this might still run (the ODBC driver replaces it with valid Teradata SQL), but it will fail if when it's part of a CREATE TABLE AS or CREATE VIEW, etc. statement and, of course, in any non-ODBC client.
Valid SQL is only:

EXTRACT(YEAR FROM col2)

Dieter

Chuckbert 78 posts Joined 07/09
19 Dec 2013

As Dieter said, there isn't a YEAR function in Teradata. Your SQL appears to be using the ODBC escape function "YEAR". Studio uses JDBC to run queries. The JDBC driver supports JDBC escape functions. The SQL that uses JDBC the escape that is equivalent the ODBC escape you've shown is:
 
Select col1, {fn YEAR(col2)} from Table1;
 
This will still indicate a syntax error in the SQL editor. This is because this is not Teradata syntax. The editor doesn't parse the JDBC escape clauses. When you use the "Execute as Individual Statements" action (which the F5 function key performs), the statement(s) in the editor are split by parsing the contents of the editor to find the individual statements. If there is a syntax error, as is when using JDBC escape clauses, that parsing will fail and result in the message you've reported.
 
You can run SQL that contains apparent errors by using the "Execute All" action of the SQL menu (and the double-triangle toolbar button). This submits the text in the editor as a single transaction. All parsing will be done by the database.

durga prasad 1 post Joined 03/15
06 Apr 2015

create table retail.employee_name

 (

 empid integer,

 empfirstname varchar(50),

 emplastname varchar(50),

 );

 

im using 14.0 version

 

 

Failed to execute sql

 

Reason:

 

Im getting parse error splitting statement

dnoeth 4628 posts Joined 11/04
07 Apr 2015

If this is your actual DDL it's the ',' before the final ')'.

Dieter

fgrimmer 553 posts Joined 04/09
07 Apr 2015

Durga, You should see red underscore in the SQL Editor to indicate you have a syntax error, as Dieter mentions above.

wesfisher 2 posts Joined 11/13
18 Sep 2015

I just upgrded from Teradata Studio 14 to 15 and now I can't run a simple nested function, like this:
select lower(trim(message_id))
from...
I get a "Parse Error Splitting Statement". Anyone know how I can rectify this?

Thanks,
Wes
 

fgrimmer 553 posts Joined 04/09
20 Sep 2015

Wes, If you press F5 or Execute as Individual, the statement will be run through the parser. Do you see red indicating syntax errors? If  you want to execute anyway, choose the 'Execute All' option, when you right click in the SQL Editor. If you think there is a parsing bug, please include the complete SQL statement and we will look into it. Be sure you have chosen the correct version of Teradata database in the Connection profile.

Chuckbert 78 posts Joined 07/09
21 Sep 2015

Wes,
I do not see the Parse Error splititng statement message when running a statement that starts with your "select lower(trim(message_id)) from".
 
Is there anything in the editor underlined with red? If so, the parse error message will appear if you use Execute as Individual Statements. Is it possible that you have some sort of non-printing characters in your statement? Those could throw off the parser. Or are there some characters that might have come from a word processor that aren't valid SQL characters? For example, if you type apostrophes into Word, they might be converted to "smart quotes" that are not valid characters in SQL. If those are copied from Word and pasted into the SQL Editor, they will be copied as is which won't be valid.

wesfisher 2 posts Joined 11/13
24 Sep 2015

Thanks for your replies. I do not se any red underlined syntax errors. And no, I'm not pasting any SQL into Studio. I'm writing all my SQL in Studio and executing it there.
Also, I am running Studio against Aster, not Teradata. Sorry for leaving that out.
I have isolated the issue. I can do a lower(trim(emp_nm)) but not a trim(lower(emp_nm)). My coworkers can run this type of query successfully. This issue began when I upgraded from TD Studio 14 to 15.
Also, since updating to Studio 15 I've been getting more pop-up "parsing" errors and less descriptive errors in the SQL History. For example, if I'm include a comma after the final column definition of a CREATE TABLE statement I now get a Parsing error, rather than "syntax error at or near ')' ".
Any other ideas anyone? Thanks in advance for any suggestions or help.
Wes
 

fgrimmer 553 posts Joined 04/09
24 Sep 2015

Wes, What version of Aster database are you running your SQL against? One thing that was added in Studio 15.10 was an Aster parser for Aster 6.10 database. If you choose Execute As Individual for Aster 6.10, it will try to split the SQL statements (one or many) using the Aster parser. You can choose Execute All option from the SQL Editor context menu (right click), to bypass it parsing for syntax errors. It appears the Aster parser has a bug with switching the order of the trim and lower. We will report this and get it resolved. As mentioned, use Execute All to get around the issue. It also appears to be more strict about the final comma on CREATE.

mlazatin 4 posts Joined 04/11
29 Jan 2016

I know the topic is about Teradata Studio 14.10, but my question is about 15.10. I didn't want to start a new topic because I'm basically getting the same "Parse Error Splitting Statement" error. I connect to Aster 6.20 using the embedded jdbc driver. My Teradata Studio is version 15.10.01.201511052035. My SQL statement.

SELECT * FROM SOMESCHEMA.SOMETABLE
ORDER BY SOMECOLUMN;

Is there a way to disable the built-in parser in Teradata Studio and just pass the SQL as-is to the database? I know @fgrimmer already mentioned the Execute All work-around, but in the long run, wouldn't it be better to have the Database do the parsing anyway?

fgrimmer 553 posts Joined 04/09
31 Jan 2016

@mlazatin, Yes, use Execute All to bypass the parser. Otherwise, Execute As Individual will use the parser to attempt to split the statement. 

mlazatin 4 posts Joined 04/11
31 Jan 2016

@fgrimmer, Thanks for the reply. Are there plans to put an option in the preferences to bypass the parser even for Execute as Individual?

fgrimmer 553 posts Joined 04/09
01 Feb 2016

@mlazatin, Not currently. We need the parser to split the SQL from the editor. We can not rely on a semicolon to determine where the statements split. 

mlazatin 4 posts Joined 04/11
11 Feb 2016

I just realized that I get a "Parse error splitting statement" whenever I have an "ORDER BY" clause without a "WHERE" clause. So as a workaround, whenever I have an order by but have really no need for a where clause, I just add a WHERE 1=1. So this:

SELECT * FROM MYTABLE
ORDER BY ID;

should be turned into:

SELECT * FROM MYTABLE
WHERE 1=1
ORDER BY ID;

@fgrimmer hopefully this gives us some clue on how to fix the "parse error splitting statement"

fgrimmer 553 posts Joined 04/09
11 Feb 2016

I was able to recreate your issue with Aster 6.1 as well. This appears to be an Aster parser error. I will pass this on to the Aster team. 

You must sign in to leave a comment.