PostgreSQL: Add condition in where clause using CASE

Question

I am using PostgreSQL 8.2 and I am also new to PostgreSQL.

I have to add one condition in the WHERE clause depending upon specific value (49) of the field (activity.type). Here is my Query:

SELECT activity.*
FROM activity 
LEFT JOIN event_types ON activity.customstatusid = event_types.id, getviewableemployees(3222, NULL) AS report 
WHERE 
( 
    CASE WHEN activity.type = 49 THEN 
    'activity.individualid IN(SELECT individualid from prospects where prospects.individualid = activity.individualid)' 
    ELSE 1 
    END 
)
AND activity.date BETWEEN '2016-10-01' AND '2016-10-06' 
AND activity.type IN (21, 22, 49, 50, 37, 199) 
AND (event_types.status = 1 or event_types.status IS NULL);

When I run above query in the command line access of PGSQL then I get below error:

ERROR:  invalid input syntax for integer: "activity.individualid IN(SELECT individualid from prospects where prospects.individualid = activity.individualid)"

What I am missing here?

What is the logic you are trying to implement inside your WHERE clause? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 6, 2016 at 10:16
I am using CASE in which I am checking activity.type=49 then returning another sub query which check activity.individualid in prospects table. This is the logic I am trying to implement — deltaforce
– deltaforce, Commented Oct 6, 2016 at 10:24
Obviously, you can't do this, but have a look at the answer by @donkopotamus, which looks like it might be what you have in mind. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 6, 2016 at 10:25
Thank you for your time as I need your help badly. Let me explain again. I need to add a condition at the run time into a WHERE that will check if the activity.type = 49 then it will make a sub query against activity.individualid for all those records that are having activity.type=49 — deltaforce
– deltaforce, Commented Oct 6, 2016 at 10:31
Your logic makes no sense, and, if taken literally, implies that the subquery should even be in a WHERE clause, let alone in a CASE statement. Show us exact input and output if you want to be completely clear here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 6, 2016 at 10:32

donkopotamus · Accepted Answer · 2016-10-06 10:30:19Z

5

Implement your where clause as:

WHERE (
    activity.type != 49 OR
    activity.individualid IN (
        SELECT individualid from prospects 
        WHERE prospects.individualid = activity.individualid)
)
AND activity.date BETWEEN '2016-10-01' AND '2016-10-06' 
AND activity.type IN (21, 22, 49, 50, 37, 199) 
AND (event_types.status = 1 or event_types.status IS NULL);

The first clause will only be true when either:

activity.type != 49; or
activity.type == 49 and activity.individualid is found in the subquery.

edited Oct 6, 2016 at 10:30

answered Oct 6, 2016 at 10:24

donkopotamus

23.4k3 gold badges58 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

deltaforce Over a year ago

I have to use sub query for activity.individualid only for the activity.type = 49. Also I have to include all types of activity.

donkopotamus Over a year ago

@deltaforce yes ... in this clause the subquery will only be relevant when activity.type = 49

Collectives™ on Stack Overflow

PostgreSQL: Add condition in where clause using CASE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related