How to write select statement with if statement in where coundition

Question

I would like to write one select statement in postgres how could I place if condition in where condition

To creating a report in that suppose we have 20 country in our table and and we have three type of employee which having contact, payroll and permanent these are the employee_category I would like to get all employee details except India and USA who having contact employee in these two country if employee having payroll and permanent should come in result

something like this

SELECT
r.employee_id AS E_ID,
r.Joining_date AS J_DATE,
r.employee_type AS E_TYPE,
'NIL'::text AS REMARKS,
i.source as SOURCE,
i.user_orgname as USER_ORGNAME
from master_employee r
INNER JOIN users_master AS i ON r.user_id = i.loginid and  i.userstatus = 'Active' 
WHERE 
if(country="india","USA"){
employee_category!='contract'
}
 and
date(r.createddate) = date(now())
ORDER BY r.createddate ASC ;

Gordon's answer is right but you may prefer: where not (country in ('india', 'USA') and employee_category = 'contract') and ... — shawnt00
– shawnt00, Commented Nov 12, 2020 at 20:09
I'm creating a report in that suppose we have 20 country in our table and and we have two type of employee which having contact, payroll and permanent these are the employee_category i would like to get all employee details except India and USA who having contact employee in these two country if employee having payroll and permanent should come in result @George query is working but the 215 record is missing — Abhinaw Anand
– Abhinaw Anand, Commented Nov 12, 2020 at 22:33

George Joseph · Accepted Answer · 2020-11-12 03:50:56Z

2

Using the case statement should work.

SELECT
r.employee_id AS E_ID,
r.Joining_date AS J_DATE,
r.employee_type AS E_TYPE,
'NIL'::text AS REMARKS,
i.source as SOURCE,
i.user_orgname as USER_ORGNAME
from master_employee r
INNER JOIN users_master AS i ON r.user_id = i.loginid and  i.userstatus = 'Active' 
WHERE employee_category <> case when country in ("india","USA") then 'contract' end
 and date(r.createddate) = date(now())
ORDER BY r.createddate ASC ;

answered Nov 12, 2020 at 3:50

George Joseph

5,93213 silver badges25 bronze badges

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

2 Comments

shawnt00 Over a year ago

The implied else in your case expression will evaluate to null and then the comparison will fail. Not sure what OP wants here exactly. Of course the error is the result of using double quotes.

Abhinaw Anand Over a year ago

I'm creating a report in that suppose we have 20 country in our table and and we have three type of employee which having contact, payroll and permanent these are the employee_category i would like to get all employee details except India and USA who having contact employee in these two country if employee having payroll and permanent should come in result @George query is working but the 215 record is missing

Gordon Linoff · Accepted Answer · 2020-11-12 03:49:09Z

1

Instead, use basic boolean logic:

where (country not in ('india', 'USA') or
       employee_category <> 'contract'
      ) and
      date(r.createddate) = date(now())

answered Nov 12, 2020 at 3:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to write select statement with if statement in where coundition

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related