How do you use ANY within a CASE statement?

Question

I have a Postgres query I'm working on that passes a parameter to the query through an API call. I've modified it to run locally in SQL Developer.

WHERE (string_to_array( name ,',')) =
CASE
    WHEN 0=1 THEN (string_to_array( name ,','))
    ELSE 
    ANY(string_to_array( 'John Smith,Steve Adam' ,','))
END

AND (string_to_array( role ,',')) =

CASE
    WHEN 0 = 1 THEN (string_to_array( Role ,','))
    ELSE 
    ANY(string_to_array( 'Manager,Analyst' ,','))
END;

When ran, I get the following error:

ERROR: syntax error at or near "ANY"

Anyone know why this is happening? Does Postgres not like my usage of ANY within the CASE statement? (Sorry if my formatting is bad)

Why do you have case statements at all? `0 = 1` is always false, leaving only the `else` branch... Perhaps that's dynamically generated by the app and sometimes it's `1 = 1` or `0 = 0`? Better to say: `where 0 = 1 or (string_to_array(...)) = any(string_to_array(...))` — Bill Jetzer, Jun 25 '20 at 19:16

score 1 · Answer 1 · answered Jun 25 '20 at 19:29

Expanding on my comment above. This seems more readable:

WHERE (0 = 1 OR (string_to_array( name ,',')) = ANY(string_to_array( 'John Smith,Steve Adam' ,',')))
  AND (0 = 1 OR (string_to_array( role ,',')) = ANY(string_to_array( 'Manager,Analyst'       ,',')))

Mike Organek · Accepted Answer · 2020-06-26T07:26:51.093

0

The ANY() has to go next to the = operator.

Please try this:

WHERE (string_to_array( name ,',')) = ANY(
  CASE
    WHEN 0=1 THEN (string_to_array( name ,','))
    ELSE string_to_array( 'John Smith,Steve Adam' ,',')
  END
)

AND (string_to_array( role ,',')) = ANY(
  CASE
    WHEN 0 = 1 THEN (string_to_array( Role ,','))
    ELSE string_to_array( 'Manager,Analyst' ,',')
  END
);

edited Jun 26 '20 at 07:26

answered Jun 25 '20 at 19:22

Mike Organek

11,647
3
11
26

How do you use ANY within a CASE statement?

2 Answers2