All Forums Database
ravishetye 4 posts Joined 04/14
06 Apr 2014
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.pdf 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.

Raja_KT 1246 posts Joined 07/09
07 Apr 2014

IS NOT IN is wrong syntactically.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Adharssh 36 posts Joined 08/13
07 Apr 2014

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.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

dnoeth 4628 posts Joined 11/04
07 Apr 2014

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 :-)

Dieter

ravishetye 4 posts Joined 04/14
08 Apr 2014

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

You must sign in to leave a comment.