Problem using DBC.SYSEXECSQL

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

Problem using DBC.SYSEXECSQL

Well, i am stumped now.  I assign a query string to a variable, as i have done in the past.  When I try to execute the query, using DBC.SYSEXECSQL, it throws an error.   The entire query is quite long, but I believe i have isolated the offending part to be:

Where VM.CALC_BASIS in ('VOL_GRP') and COA.YR_PD_NUM > 201800

when executing, I get: CALL Failed. [3706] PBC_VMR_PROC:Syntax error: expected something between a string or Unicode character literal and the word 'VOL_GRP'.

previously, i had tried the above code as 

VM.CALC_BASIS = 'VOL_GRP'

But I got the same error.  Now here is the weird part...   I can insert the query string into a log table and then copy the query from the log table into SQL Assistant, and the entire query runs fine.  It is just something about trying to execute the query in DBC.SYSEXECSQL that is failing.  Thanks in advance... 


Accepted Solutions
Ambassador

Re: Problem using DBC.SYSEXECSQL

First you assigned a string with embedded single quotes to a variable by doubling the single quotes:

 

   SET v_InsStmt = ' Where VM.CALC_BASIS in (''' || v_CALC_BASIS || ''')' ||
     ' and COA.YR_PD_NUM > 201800 ';

Let's see what's actually assigned to v_InsStmt:

 

 

SELECT 'bla' AS v_CALC_BASIS,
     ' Where VM.CALC_BASIS in (''' || v_CALC_BASIS || ''')' ||
     ' and COA.YR_PD_NUM > 201800 ';

bla   Where VM.CALC_BASIS in ('bla') and COA.YR_PD_NUM > 201800 

Now you try to pass it with its embedded single quotes as a string to v_InsStmt:

     set v_LogStmt =  'insert into DLAB_CIO.AAA_TB_LOG values ('''||v_InsStmt||''')';

Replacing v_InsStmt with it's actual data shows this result:

'insert into DLAB_CIO.AAA_TB_LOG values ('''||' Where VM.CALC_BASIS in ('bla') and COA.YR_PD_NUM > 201800 '||''')'

When you cut & paste this into SQL Assistant/Teradata Studio you will immediately notice that the "bla" has a different color, because it's single quoted.

Now you could double the embedded single quotes using oReplace:

   SET v_LogStmt =  'insert into DLAB_CIO.AAA_TB_LOG values ('''||OReplace(v_LogStmt, '''', '''''')||''')';

But there's no need for Dynamic SQL, as you already know the exact insert statement you better write it as-is:

INSERT INTO DLAB_CIO.AAA_TB_LOG VALUES (v_InsStmt);

Additional advantage: You get an error message if your syntax is not correct while compiling the SP not when you CALL it.

 

Dealing with quotes in a Dynamic SQLstring is tricky, you should avoid it if possible :-)

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
6 REPLIES 6
Teradata Employee

Re: Problem using DBC.SYSEXECSQL

So you do something such as (oversimplified):

 

SET myvar = 'UPDATE ... WHERE VM.CALC_BASIS=''VOL_GRP'' AND COA.YR_PD_NUM > 201800';
CALL dbc.SysExecSQL(myvar);

 That should work. But SysExecSQL is old syntax; you could try this:

EXECUTE IMMEDIATE myvar;

 Note that if the SQL statement returns data you will need to use EXECUTE with a cursor (SysExecSQL does not allow data return).

EXECUTE IMMEDIATE myvar;

Enthusiast

Re: Problem using DBC.SYSEXECSQL

Thanks a lot Fred, i will start using execute immediate, since it is the newer syntax.  Unfortunately, i still get the same error... I realize i should have shown you the code i used to create this clause that is casing the issues...  Again, the clause is:

Where VM.CALC_BASIS in ('VOL_GRP') and COA.YR_PD_NUM > 201800

 

OK, feel free to laugh... here is the code i used to generate it... i kept trying single quotes until i get the output i wanted... this is what i am using to generate the clause above... note that v_CALC_BASIS is a varaible that contains the string 'VOL_GRP'.  I tried fewer single quotes and could not get it to come out right...

    ' Where VM.CALC_BASIS in ('||''''''||v_CALC_BASIS||''''''||')'||
    ' and COA.YR_PD_NUM > 201800 ';

 Regardless of how weird it looks, it seems to create output that looks good...

Ambassador

Re: Problem using DBC.SYSEXECSQL

You added too many quotes :-)

 

To get a single quote you need to double it:

    ' Where VM.CALC_BASIS in (''' || v_CALC_BASIS || ''')' ||
    ' and COA.YR_PD_NUM > 201800 '
Enthusiast

Re: Problem using DBC.SYSEXECSQL

That totally makes sense, thanks.  However, if I set it up exactly like that (SyntaxEditor Code Snippet

   ' Where VM.CALC_BASIS in (''' || v_CALC_BASIS || ''')' ||
     ' and COA.YR_PD_NUM > 201800 ';

I cannot even insert the text of the query into a log table

SyntaxEditor Code Snippet

     set v_LogStmt =  'insert into DLAB_CIO.AAA_TB_LOG values ('''||v_InsStmt||''')';
     Execute Immediate v_LogStmt;

 I get that same 3707 error that is shown in my original post.  So in this case, i am trying to just insert the query string into a table, not even execute it.  And again, if i just comment out these last 2 lines, I can insert the query string into the log table and also successfully execute it.

Ambassador

Re: Problem using DBC.SYSEXECSQL

First you assigned a string with embedded single quotes to a variable by doubling the single quotes:

 

   SET v_InsStmt = ' Where VM.CALC_BASIS in (''' || v_CALC_BASIS || ''')' ||
     ' and COA.YR_PD_NUM > 201800 ';

Let's see what's actually assigned to v_InsStmt:

 

 

SELECT 'bla' AS v_CALC_BASIS,
     ' Where VM.CALC_BASIS in (''' || v_CALC_BASIS || ''')' ||
     ' and COA.YR_PD_NUM > 201800 ';

bla   Where VM.CALC_BASIS in ('bla') and COA.YR_PD_NUM > 201800 

Now you try to pass it with its embedded single quotes as a string to v_InsStmt:

     set v_LogStmt =  'insert into DLAB_CIO.AAA_TB_LOG values ('''||v_InsStmt||''')';

Replacing v_InsStmt with it's actual data shows this result:

'insert into DLAB_CIO.AAA_TB_LOG values ('''||' Where VM.CALC_BASIS in ('bla') and COA.YR_PD_NUM > 201800 '||''')'

When you cut & paste this into SQL Assistant/Teradata Studio you will immediately notice that the "bla" has a different color, because it's single quoted.

Now you could double the embedded single quotes using oReplace:

   SET v_LogStmt =  'insert into DLAB_CIO.AAA_TB_LOG values ('''||OReplace(v_LogStmt, '''', '''''')||''')';

But there's no need for Dynamic SQL, as you already know the exact insert statement you better write it as-is:

INSERT INTO DLAB_CIO.AAA_TB_LOG VALUES (v_InsStmt);

Additional advantage: You get an error message if your syntax is not correct while compiling the SP not when you CALL it.

 

Dealing with quotes in a Dynamic SQLstring is tricky, you should avoid it if possible :-)

 

 

 

 

 

 

 

 

 

Enthusiast

Re: Problem using DBC.SYSEXECSQL

You are right, the single quotes in the dynamic SQL are extremely tricky!  Thanks so much for the help, all is good now.  One other thing that would have helped is for me to have a way to do something like a "print" or a "put_line" to see the conents of the string on the screen.  What is the best way to do that?