select case when exists (subquerry)

Database
Enthusiast

select case when exists (subquerry)

Hi, i get an error: 

 Failed [3771 : HY000] Illegal expression in WHEN clause of CASE expression. 

 

when trying to run follow statement:

CREATE MULTISET VOLATILE TABLE tt_testVIP, NO LOG AS
(
select case when exists
( select distinct 1 as"1"
from tbl1 a
inner join tt_CID_PlayerID_Split b on A.CID = b.CID_Player_ID ) then 1 else 0 end Vip) WITH DATA
ON COMMIT PRESERVE ROWS;

 

any help how to convert it to right statement?

4 REPLIES
Senior Apprentice

Re: select case when exists (subquerry)

Hi,

 

Providing 'tbl1' always has at least 1 row in it then I think the following will work for you.

SELECT MAX(CASE WHEN b.cid_player_id IS NOT NULL THEN 1 ELSE 0 end) AS vip
  FROM tbl1 a 
  LEFT OUTER JOIN tt_CID_PlayerID_Split b 
  ON A.CID = b.CID_Player_ID;

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: select case when exists (subquerry)

Hi Dave, seems it works fine, tnx a lot!

Highlighted
Teradata Employee

Re: select case when exists (subquerry)

Try:
... select case
( select distinct 1 as"1"
from tbl1 a
inner join tt_CID_PlayerID_Split b on A.CID = b.CID_Player_ID )
when 1 then 1 else 0 end Vip ...

Enthusiast

Re: select case when exists (subquerry)

TNX a lot - works!