PostgreSQL: Add condition in where clause using CASE
11,629
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
; oractivity.type == 49
andactivity.individualid
is found in the subquery.
Author by
deltaforce
Updated on June 04, 2022Comments
-
deltaforce about 2 years
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?
-
Tim Biegeleisen over 7 yearsWhat is the logic you are trying to implement inside your
WHERE
clause? -
deltaforce over 7 yearsI 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
-
Tim Biegeleisen over 7 yearsObviously, 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.
-
deltaforce over 7 yearsThank 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
-
Tim Biegeleisen over 7 yearsYour logic makes no sense, and, if taken literally, implies that the subquery should even be in a
WHERE
clause, let alone in aCASE
statement. Show us exact input and output if you want to be completely clear here. -
deltaforce over 7 yearsSolution given by @donkopotamus worked. Sorry, as I was not plain enough.
-
-
deltaforce over 7 yearsI 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 7 years@deltaforce yes ... in this clause the subquery will only be relevant when
activity.type = 49