I have a table:

create table test_table
(customer_num  NUMBER,
 dep_group     VARCHAR2(50),
 dep_type      VARCHAR2(50),
 balance       NUMBER)

the table has two records:

                  " test_table "
 customer_num   dep_group   dep_type    balance       
     1            x           x1          10
     1            x           x2          10

The query below must return two records of the table but it does not, and it's weird!!

select *
from test_table
where ((dep_group = 'x') AND (dep_type = 'x1') AND ((balance) > 1)
     ((dep_group = 'x') AND (dep_type = 'x2') AND ((balance) > 1)))

when I run each part of the query separately , it gives me the desired record

 select *
 from test_tablet t
 where (dep_group = 'x') AND (dep_type = 'x1') AND ((balance) > 1) 

 select *
 from test_tablet t
 where (dep_group = 'x') AND (dep_type = 'x2') AND ((balance) > 1) 

I mean customer = 1 matches both conditions and the original query must give me the two records of the table. Is there something wrong with my query or my understanding of the logical operators?

Thanks in advance.

  • 1
    dep_type cannot possibly be equal to 'x1' AND 'x2' at the same time, can it
    – mustaccio
    May 26 '20 at 12:01
  • @mustaccio no of course it can not.
    – Pantea
    May 26 '20 at 12:08
  • 2
    And yet this is exactly what your first query looks for.
    – mustaccio
    May 26 '20 at 12:57

As Mustaccio mentioned in the comments, you want rows where dev_type is either 'x1' or 'x2', not where it's 'x1' and 'x2'.

Also you only need the brackets around each of the alternative sets of conditions. The others aren't doing anything and they are just confusing.

with test_table (customer_num, dep_group, dep_type, balance) as
     ( select 1, 'x', 'x1', 10 from dual union all
       select 1, 'x', 'x2', 10 from dual
select *
from  test_table
where (dep_group = 'x' and dep_type = 'x1' and balance > 1)
or    (dep_group = 'x' and dep_type = 'x2' and balance > 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.