IS NOT IN Syntax

Database

IS NOT IN Syntax

One of our customers provided us their teradata workload for analysis.

There are queries like

SELECT t1.c1 
,CASE
WHEN t1.c2 IS NOT NULL
AND t1.c2 IS NOT IN ( 'P' , 'PH' ) THEN t1.c4
WHEN t1.c3 IS NOT NULL
AND t1.c3 IS NOT IN ( 'P' , 'PH' ) THEN t1.c5
END AS c6 from t1

We are not able to parse this query since we expected "NOT IN" instead of "IS NOT IN" in our grammar.

I checked the usage manual at page 107 of http://tunweb.teradata.ws/tunstudent/TeradataUserManuals/SQL_Reference_--_Data_Manipulation_Language... which does not mention the IS keyword while using NOT IN

Can some one please validate if the usage IS NOT IN is correct or not.

4 REPLIES
Enthusiast

Re: IS NOT IN Syntax

IS NOT IN is wrong syntactically.

Enthusiast

Re: IS NOT IN Syntax

Hi Ravi,

There is no syntax called 'IS NOT IN'. We have NOT IN or IN. Please find the Correct Code.

SELECT t1.c1         ,
CASE WHEN t1.c2 IS NOT NULL AND t1.c2 NOT IN ( 'P' , 'PH' )
THEN t1.c4
WHEN t1.c3 IS NOT NULL AND t1.c3 NOT IN ( 'P' , 'PH' )
THEN t1.c5 END AS c6
from t1

Thanks & Regards,

Adharssh Rao.







Junior Contributor

Re: IS NOT IN Syntax

This is probably also a leftover of Teradata's pre-SQL query language, Tequel.

Syntatically correct, but proprietary to Teradata and deprecated since at least 20 years :-)

Re: IS NOT IN Syntax

Thanks dnoeth, Our customer is more than 60 years old company, so the Tequel connection might just make sense.