Multiple values in where clause

Database

Multiple values in where clause

I have following query in Teradata 13.10 which is giving error.

select * from WEEK_LU where wk_key in (201401+5, 201402+5);

The error is "SELECT Failed.  [3706] Syntax error: expected something between an integer and ','. ".

I even tried 

select * from WEEK_LU where wk_key in (cast(201401+5 as int), cast(201402+5 as int));

but got same error.

Can me please help me solve this.

Thanks,

Karun

4 REPLIES
N/A

Re: Multiple values in where clause

Hi Karun,

I don't know why but Teradata doesn't support any calculation within IN :-(

You need to rewrite it using OR instead:

select * from WEEK_LU 
where wk_key = 201401+5
or wk_key = 201402+5;

Re: Multiple values in where clause

Hi Dieter,

We have below scenario. Please help us with this

Table A

Product_nm Product_id Ar_CD offercode

A 10 2 10

A 10 3 10

B 11 17 11

B 11 18 11

B 11 19 11

Table B

Product_nm Product_id Ar_CD offer_code

A 10 2 8

A 10 3 8

B 11 17 1

B 11 18 1

B 11 20 1

I need data like below

Product_nm Product_id offer_code

A 10 10

B 11 1

When Ar_cd for each product (table_a) is subset of  table B

i.e, for A - > Ar_CD (2,3) from Table A are present in Table B , so offcode should come as 10

for B, 17,18 are matching but 19 is not present in table B so offer_code should come as 1

How to implement this in where clause i.e, checking subset of data

Please help us

Re: Multiple values in where clause

Hi dvya,

I am not clear with the requirement since I feel there are many more CASES for which requirement has not been mentioned.

Like is the PRODUCT_ID would be same for same PRODUCT_NM? Whats the result when B has offer code (17,18,19,20)? According to my understanding I have done the following and hope your requirement is met:

/* Creating test data */
CREATE MULTISET VOLATILE TABLE VT_TABLE_A ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
NO LOG
(
PRODUCT_NM VARCHAR(1),
PRODUCT_ID INTEGER,
AR_CD INTEGER,
OFFERCODE INTEGER
)
PRIMARY INDEX (PRODUCT_NM)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TABLE_A VALUES ('A',10,2,10);
INSERT INTO VT_TABLE_A VALUES ('A',10,3,10);
INSERT INTO VT_TABLE_A VALUES ('B',11,17,11);
INSERT INTO VT_TABLE_A VALUES ('B',11,18,11);
INSERT INTO VT_TABLE_A VALUES ('B',11,19,11);

CREATE MULTISET VOLATILE TABLE VT_TABLE_B ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
NO LOG
(
PRODUCT_NM VARCHAR(1),
PRODUCT_ID INTEGER,
AR_CD INTEGER,
OFFERCODE INTEGER
)
PRIMARY INDEX (PRODUCT_NM)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TABLE_B VALUES ('A',10,2,8);
INSERT INTO VT_TABLE_B VALUES ('A',10,3,8);
INSERT INTO VT_TABLE_B VALUES ('B',11,17,1);
INSERT INTO VT_TABLE_B VALUES ('B',11,18,1);
INSERT INTO VT_TABLE_B VALUES ('B',11,20,1);
/* Completed creating test data */

/* Your output */
SELECT
A.PRODUCT_NM
, A.PRODUCT_ID
, CASE
WHEN A.CNT = COALESCE(B.CNT,0)
THEN A.OFFERCODE
ELSE B.OFFERCODE
END AS OFFERCODE
FROM
(
SELECT
A.PRODUCT_NM
, A.PRODUCT_ID
, A.AR_CD
, A.OFFERCODE
, COUNT(A.AR_CD) OVER (PARTITION BY A.PRODUCT_NM,A.PRODUCT_ID) AS CNT
FROM
VT_TABLE_A A
INNER JOIN
VT_TABLE_B B
ON A.PRODUCT_NM = B.PRODUCT_NM
AND A.PRODUCT_ID = B.PRODUCT_ID
AND A.AR_CD = B.AR_CD
) A
LEFT OUTER JOIN
(
SELECT
PRODUCT_NM
, PRODUCT_ID
, AR_CD
, OFFERCODE
, COUNT(AR_CD) OVER (PARTITION BY PRODUCT_NM,PRODUCT_ID) AS CNT
FROM
VT_TABLE_B
) B
ON A.PRODUCT_NM = B.PRODUCT_NM
AND A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY 1,2,3;

Thanks,

Rohan Sawant

N/A

Re: Multiple values in where clause

This seems to be the same requirement as this question: Need help to create SQL query

    SELECT
A.PRODUCT_NM
, A.PRODUCT_ID
, CASE WHEN COUNT(*) = COUNT(B.PRODUCT_NM) THEN MIN(A.OFFERCODE) ELSE MIN(B.OFFERCODE) end
FROM
VT_TABLE_A A
LEFT JOIN
VT_TABLE_B B
ON A.PRODUCT_NM = B.PRODUCT_NM
AND A.PRODUCT_ID = B.PRODUCT_ID
AND A.AR_CD = B.AR_CD
GROUP BY 1,2