Semi Colon in SQL String

Azzuri · Post by **Azzuri** » Tue Mar 26, 2013 12:21 pm

Hi All, trying to pass a SQL statement to DataStage job to execute:

INSERT INTO TABLEA (Col1) VALUES('This ; is a test' ;

Doesn't like the semi colon in the string
teraTargetTable: RDBMS code 3707: Syntax error, expected something like ',' or ')' between a string or a Unicode character literal and the word

Any ideas on how to get around this error

Thanks In Advance

Azzuri · Post by **Azzuri** » Tue Mar 26, 2013 12:32 pm

Sorry, forgot to mention it's in the After SQL of the teradata Connector.

Also I missed the the closing braket in my previous post.

INSERT INTO TABLEA (Col1) VALUES('This ; is a test') ;

chulett · Post by **chulett** » Tue Mar 26, 2013 1:09 pm

What happens when you leave off the semi-colon?

Azzuri · Post by **Azzuri** » Tue Mar 26, 2013 1:41 pm

If I remove the ; from the string it works fine.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 26, 2013 1:44 pm

Can you insert this string using Teradata SQL Assistant, or some other client?

That is, is it a Teradata restriction or a DataStage problem?

chulett · Post by **chulett** » Tue Mar 26, 2013 1:52 pm

It's more of a DataStage thing it would seem. Found out long ago that (for Oracle in my case) it didn't want the trailing semi-colon that Toad would add and didn't mind if it was there. People would "user define" something that wouldn't run simply because of the semi-colon.

I don't see the issue here, take it out and you're fine.

Azzuri · Post by **Azzuri** » Tue Mar 26, 2013 1:58 pm

Works fine in Teradata Assistant. I only get the error in DataStage.

Azzuri · Post by **Azzuri** » Tue Mar 26, 2013 2:07 pm

Craig, unfortnately the semi-colon is part of the source data.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 26, 2013 3:23 pm

Other than fiddling around with escaping either the quote characters or the semi-colon character I have no suggestions. Perhaps you should involve your official support provider - this seems to me like incorrect behaviour.

chulett · Post by **chulett** » Tue Mar 26, 2013 4:06 pm

Oh heck, I thought we were talking about the trailing semi-colon, not the one in your data... sorry. What happens if you escape it? Definitely involve your support provider as it certainly does seem like bad behaviour and perhaps there is a fix/patch for it out there.

Azzuri · Post by **Azzuri** » Wed Mar 27, 2013 5:35 am

Hey Craig. I tried to escape it without any luck. I might open a ticket with IBM. Thanks for your help.