Clickhouse subquery not allowed with CASE/IF statement-CodePudding

I am trying to filter data with the help of a condition in click house. This is the query:

SELECT CASE

WHEN EXISTS (

SELECT * FROM (SELECT Time, Lon, Users FROM table1 ORDER BY Time DESC LIMIT 5) WHERE Users>=75

)

THEN ( * FROM (SELECT Time, Lon, Users FROM table1 ORDER BY Time DESC LIMIT 5) WHERE Users<75)

ELSE ( * WHERE 0=1)

END;

I had also tried simple IF condition in clickhouse but eve in that subquery didn't work. The query:

SELECT if((SELECT count() FROM table1 WHERE Users>=75)>0, SELECT * FROM table1 WHERE Users<75, plus(2, 6));

Both time the basic error remains same:

Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 77 ('default'): default.4g WHERE Users<75, plus(2, 6));
. Expected one of: LIKE, GLOBAL NOT IN, end of query, AS, DIV, IS, OR, QuestionMark, BETWEEN, NOT LIKE, MOD, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, token, NOT IN, GLOBAL IN (version 21.3.20.1 (official build))

Is there any way to run subqueries within CASE/IF in click house ?

CodePudding user response：

You can use EXISTS directly in the WHERE clause:

SELECT Time, Lon, Users 
FROM table1 
WHERE Users < 75
  AND EXISTS (SELECT * FROM table1 WHERE Users >= 75)
ORDER BY Time DESC LIMIT 5;

CodePudding user response：

This is achievable using OR condition.

select Time, Lon, Users
from table1
where Users < 75 or Users >= 75