CODEWITHSUNDEEP
sqloracle-database
user3199077
user3199077

Reputation: 57

Condition in oracle sql query

I have a query like

 select * from emp where emp.id in(1,2,3) and emp.sal>10000

I need to add a logic to the check emp.sal>10000 only if design.type='Labor', I tried like the below but not working

select * from emp,design where emp.id in(1,2,3) and case when design.type='Labor' then emp.sal>10000

Can we implement this ?

Upvotes: 0

Views: 37

Answers (4)

user2989408
user2989408

Reputation: 3137

This should work for you...

SELECT * FROM emp 
WHERE emp.id IN (1,2,3) 
   AND ((design.type = 'Labor' AND emp.sal>10000) 
        OR (design.type <> 'Labor'))

The simplification of the OR condition would be..

SELECT * FROM emp 
WHERE emp.id IN (1,2,3) 
   AND (design.type <> 'Labor' OR emp.sal > 10000)

Upvotes: 0

VJ Hil
VJ Hil

Reputation: 904

  Try this.
  select * from 
  (
    select * from emp where id in (1,2,3) and design.type='Labor'
  )a
  where a.sal>10000;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311308

You want to query rows that have a salary higher than 10000 only when the type is labor for completion's sake, we will also state that for different types we don't care about the salary. If we put this formally, we'll get:

(type = 'Labor' and salary > 10000) OR (type != 'Labor')

This boolean expression can of course be simplified - we're looking for rows that either have a type different than Labor or a salary higher than 10000:

(salary > 10000) OR (type != 'Labor')

So if we put it all together:

SELECT *
FROM   emp 
WHERE  (emp.id IN (1,2,3)) AND (type != 'Labor' OR sal > 10000)

Upvotes: 0

Yves Dubois
Yves Dubois

Reputation: 943

Simple:

select * from emp where emp.id in(1,2,3) and (design.type<>'Labor' OR emp.sal>10000)

If design.type='Labor' then emp.sal must be greater than 10000 for the condition to be true.

Upvotes: 1

Related Questions