postgres: use of CASE and ANY() in WHERE clause
Is there some way to make this work?
SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
t.tid =
CASE
WHEN t2.someboolval THEN ANY(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16])
ELSE ANY(ARRAY[77,66])
END
Unfortunately I can't just do t.tid = CASE WHEN t2.someboolval THEN 1 ELSE 2 END
because I need to match against an array. Is 开发者_如何学JAVAthis doable?
Use AND/OR. Something like:
SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
t2.someboolval AND t.tid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)
OR NOT (t2.someboolval) and t.id IN (77,66)
Edit: formatted
You have to change place of ANY:
SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
t.tid =
ANY(CASE
WHEN t2.someboolval THEN ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16]
ELSE ARRAY[77,66]
END)
精彩评论