Is it possible to use an expression list with where-in clause in Teradata

Question

Here are the Oracle docs for what I'm trying to do. Is the third example from the doc possible in Teradata?

I created a SQL fiddle that shows my problem. I only want to select orders for Corvette generation 7 and Mustang generation 1 vehicles. This works fine in oracle, but when I tried to execute it from a Teradata session, it fails

SELECT Failed. 3707: Syntax error, expected something like a 'SELECT' keyword or '(' or a 'NONTEMPORAL' keyword or 'AS' keyword between '(' and the integer '120777'.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE CAR_ORDERS
(
    ORD_ID INTEGER NOT NULL,
    MODEL_NAME VARCHAR2(20) NOT NULL,
    GENERATION INTEGER NOT NULL,
    BEGIN_YEAR INTEGER NOT NULL,
    END_YEAR INTEGER NOT NULL
 );


INSERT INTO CAR_ORDERS VALUES(1,  'CORVETTE', 1, 1953, 1962);
INSERT INTO CAR_ORDERS VALUES(2,  'CORVETTE', 1, 1953, 1962);
INSERT INTO CAR_ORDERS VALUES(3,  'CORVETTE', 1, 1953, 1962);
INSERT INTO CAR_ORDERS VALUES(4,  'CORVETTE', 1, 1953, 1962);
INSERT INTO CAR_ORDERS VALUES(5,  'CORVETTE', 2, 1963, 1967);
INSERT INTO CAR_ORDERS VALUES(6,  'CORVETTE', 3, 1968, 1982);
INSERT INTO CAR_ORDERS VALUES(7,  'CORVETTE', 4, 1984, 1996);
INSERT INTO CAR_ORDERS VALUES(8,  'CORVETTE', 5, 1997, 2004);
INSERT INTO CAR_ORDERS VALUES(9,  'CORVETTE', 6, 2005, 2013);
INSERT INTO CAR_ORDERS VALUES(10, 'CORVETTE', 6, 2005, 2013);
INSERT INTO CAR_ORDERS VALUES(11, 'CORVETTE', 7, 2014, 2016);
INSERT INTO CAR_ORDERS VALUES(12, 'CORVETTE', 7, 2014, 2016);
INSERT INTO CAR_ORDERS VALUES(13, 'CORVETTE', 7, 2014, 2016);
INSERT INTO CAR_ORDERS VALUES(14, 'CORVETTE', 7, 2014, 2016);
INSERT INTO CAR_ORDERS VALUES(15, 'MUSTANG', 1, 1964, 1973);

Query 1:

SELECT *
  FROM CAR_ORDERS CO
 WHERE (CO.MODEL_NAME, CO.GENERATION) IN (('CORVETTE', 7), ('MUSTANG', 1))

Results:

| ORD_ID | MODEL_NAME | GENERATION | BEGIN_YEAR | END_YEAR |
|--------|------------|------------|------------|----------|
|     11 |   CORVETTE |          7 |       2014 |     2016 |
|     12 |   CORVETTE |          7 |       2014 |     2016 |
|     13 |   CORVETTE |          7 |       2014 |     2016 |
|     14 |   CORVETTE |          7 |       2014 |     2016 |
|     15 |    MUSTANG |          1 |       1964 |     1973 |

Sudhanshu Agrawal Sudhanshu Agrawal · Accepted Answer · 2015-08-06T18:00:00

You can do something like this in TERADATA:

CREATE TABLE CAR_ORDERS
AS SELECT * FROM TABLE_NAME 
WHERE   
(MODEL_NAME='CORVETTE' and GENERATION=7) 
OR  
(MODEL_NAME='MUSTANG' and GENERATION=1)
WITH DATA;

If this works it is good, if not then try creating the table first and then use the INSERT QUERY:

INSERT INTO CAR_ORDERS
SELECT * FROM TABLE_NAME 
WHERE   
(MODEL_NAME='CORVETTE' and GENERATION=7) 
OR  
(MODEL_NAME='MUSTANG' and GENERATION=1);

Is it possible to use an expression list with where-in clause in Teradata

1 Answers