CODEWITHSUNDEEP
sqloracle-databasecasemultiple-conditions
SotaNice
SotaNice

Reputation: 79

Multiple AND conditions in case statement

I've got an Oracle table CUST with Fields YEAR and STATUS. I'm trying to write a statement that will select the records where ((CUST.YEAR = '2017' and CUST.STATUS = 'ACTIVE') and where (CUST.YEAR = '2018' and CUST.STATUS = 'ACTIVE')). When both of these statements are true, I want to return '1' else '0'

select *
from cust
where cust.year = '2017' and cust.status = 'Active'

returns the correct number of rows (394).

select *
from cust
where cust.year = '2018' and cust.status = 'Active'

returns the correct number of rows (451).

Here's where the wheels fall off (due to my inexperience with SQL scripting). I've tried to combine the two select statements and I either gets tens of thousands of rows or errors because of incorrect syntax. This is before even attempting to use a case statement to return a comparative result (if both of these conditions, then '1' else '0').

I realize this is probably pretty elementary stuff but the syntax is beyond me as of now. Would someone be kind enough to help me construct this statement?

The few times I've posted to this forum I've learned things that help to make me more self-sufficient, so I offer my thanks in advance.

Upvotes: 1

Views: 1397

Answers (3)

APC
APC

Reputation: 146229

If I have understood your requirement correctly you want to determine whether your table has records for both pairs of conditions, that is , whether you have active records for both 2017 and 2018. The solutions provided so far will assert whether either condition is true but not both.

So here is a solution which satisfies your actual requirement. We have a WITH clause which selects one active record for each year (which is all your need). The inline view then counts how many records it found. If the count is two then you have active records for both years. 

with tst as (
    select cust.cust_id, cust.year
    from   cust
    where  cust.year   = '2017'
    and    cust.status = 'Active' 
    group by cust.cust_id, cust.year
    union  all
    select cust.cust_id, cust.year
    from   cust
    where  cust.year   = '2018'
    and    cust.status = 'Active' 
    group by cust.cust_id, cust.year
)
select cust_id
       , case when cnt = 2 then 1 else 0 end as your_condition
from ( select cust_id, count(*) as cnt
       from tst
       group by cust_id )
/

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

The way I understood it, or might be what you're looking for, i.e.

select *
from cust
where (cust.year = '2017' and cust.status = 'Active')
   or (cust.year = '2018' and cust.status = 'Active');

which - as William says - leads to

where cust.status = 'Active'
  and cust.year in ('2017', '2018')

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You can take advantage of IN here:

select *
from cust
where cust.year IN ('2017', '2018') and
      cust.status = 'Active'

Upvotes: 1

Related Questions