All Forums Database
karun.poudel 2 posts Joined 06/13
24 Feb 2015
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

dnoeth 4628 posts Joined 11/04
24 Feb 2015

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;

 

Dieter

divyagolla 22 posts Joined 02/14
25 Feb 2015

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

 

Rohan_Sawant 55 posts Joined 07/14
25 Feb 2015

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

 

dnoeth 4628 posts Joined 11/04
25 Feb 2015

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

 

Dieter

You must sign in to leave a comment.