Error 3706 in select where clause "not in" expression

General

Error 3706 in select where clause "not in" expression

I've shortened the query for the sake of simplicity but I cannot figure this out:

 

In Teradta SQL Assistant...

 

select * from SomeTableName where SomeColumn not in ('Text1','Text2')

 

I get: SELECT Failed. [3706] Syntax error: expected something between '(' and the string 'Text1'.

 

Yes, I know it's a syntax error... but in my research, the syntax is correct and I'm trying to avoid a lot of "OR's" and "AND's".

 

Any ideas?

7 REPLIES 7
Ambassador

Re: Error 3706 in select where clause "not in" expression

Hi,

Have you copied the text from somewhere else and have ended up with 'smart quotes' instead of 'straight quotes'?

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Error 3706 in select where clause "not in" expression

Yes, I thought of that too. Odd, I see simple examples on various websites but then I see solutions on other sites by users and they say that you have to specify... like "where ThisColumn <> 'sometext'"..

Ambassador

Re: Error 3706 in select where clause "not in" expression

Hi,

 

As far as I can tell that syntax is valid for Teradata. Are you using an ODBC connection and that is doing some parsing/conversion. There is an option to 'disable parsing', it might be worth playing with that or trying the same code using a .NET connection or from studio or bteq. (yeah, you guessed it, I'm out of ideas...)

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Error 3706 in select where clause "not in" expression

I've tried in SSMS using a linked server and openquery and in SQL Assistant, same error returns. It's like the database itself doesn't support it...

Ambassador

Re: Error 3706 in select where clause "not in" expression

Possibly a red-herring...

 

Is either your column name or your table name a reserved word? I'd expect a different error, but who knows...

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Ambassador

Re: Error 3706 in select where clause "not in" expression

Something to try:

 

Get query logging turned on for your username and make sure the entire sql text is captured.

 

Run the query, get the error and then check in the dbql table to see what sql is being received by the dbms. That should confirm whether it is a dbms issue or whether it is a driver/api 'conversion' issue.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Error 3706 in select where clause "not in" expression

Thanks but I think I'll have to deal with it as inefficient as it is...