Unable to create stored proc with newline character

Teradata Studio
Enthusiast

Unable to create stored proc with newline character

hello,

 

I am attempting to create/replace a stored procedure that contains the X'0A' new line character, but I am getting the 'Failed to execute SQL:Parse Error splitting statement' error. In SQL Assistant this runs fine. Teradata Studio version 15.10.01.01.201601251532.

 

REPLACE PROCEDURE
...
  FOR
        C1 AS c1
        CURSOR FOR
            SELECT
                columnname
            FROM
                dbc.COLUMNS
            WHERE
                tablename = :ODS_TBL
                AND databasename = :ODS_DB
                AND COLUMNTYPE NOT IN ('CO', 'BO')
                AND ColumnName <> 'ODS_EXP_ROW_DTM'
                AND ColumnName <> 'ODS_CREATE_ROW_DT'
                AND ColumnName <> 'ODS_SID'
            ORDER BY columnname ASC
    DO
		SET MERGE_STATEMENT = MERGE_STATEMENT
		|| TRIM(C1.COLUMNNAME)
		|| ','
		|| X'0A';
    END FOR;
...

Accepted Solutions
Teradata Employee

Re: Unable to create stored proc with newline character

Looking at the SQL Data Types and Literals document, I don't see that

X'0A'

is a valid representation of the character. It shows instead that a hexadecimal character literal would be formed as

'0A'XC

I see that the X'0A' form is accepted when the SQL is submitted. I'll have to investigate why I can't find that form documented.

 

In the meantime, there are several ways to submit SQL from the SQL Editor. The default way (Execute as Individual Statements), the way you are using, submits the statements in the SQL editor individually. In order to do that, it parsed the statements to find the individual statments. When there is a syntax error, you get the "unable to split statements" message. A recent change has been made to simply split at semicolons when there are syntax errors.

 

You can use the "Execute All" choice. That is the double-green-triangle choice in the SQL Editor's toolbar (selected from the drop-down list of the green triangle(s) run button), or by right-clicking in the SQL Editor and selecting "Execute All" from the context menu. This submits the contents of the SQL Editor as a single statement and the Teradata System will parse and split the statements. When you use Execute All, there is no parsing of the contents of the editor before submitting. 

 

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Unable to create stored proc with newline character

Looking at the SQL Data Types and Literals document, I don't see that

X'0A'

is a valid representation of the character. It shows instead that a hexadecimal character literal would be formed as

'0A'XC

I see that the X'0A' form is accepted when the SQL is submitted. I'll have to investigate why I can't find that form documented.

 

In the meantime, there are several ways to submit SQL from the SQL Editor. The default way (Execute as Individual Statements), the way you are using, submits the statements in the SQL editor individually. In order to do that, it parsed the statements to find the individual statments. When there is a syntax error, you get the "unable to split statements" message. A recent change has been made to simply split at semicolons when there are syntax errors.

 

You can use the "Execute All" choice. That is the double-green-triangle choice in the SQL Editor's toolbar (selected from the drop-down list of the green triangle(s) run button), or by right-clicking in the SQL Editor and selecting "Execute All" from the context menu. This submits the contents of the SQL Editor as a single statement and the Teradata System will parse and split the statements. When you use Execute All, there is no parsing of the contents of the editor before submitting. 

 

Enthusiast

Re: Unable to create stored proc with newline character

Thanks! Replacing the 

X'0A'

with

 

'0A'XC

worked as expected.