CODEWITHSUNDEEP
sqloracle-database
lostinthebits
lostinthebits

Reputation: 661

pl/sql case statement results in modifying two fields

I have a select statement. This is how it normally works when one condition effects one field:

select 
  case when <condition 1>
  then '1'
  end as field1
, case when <condition 2>
  then '2'
  end as field2

That works fine to finalize one field (field1 or field2 for example). But what if the condition is supposed to impact two fields. I can't use the current select structure as far as I can see.

Do I need to repeat the condition (so the same condition causes an effect in two separate fields (as below)?

select 
  case when <condition 1>
  then '1'
  end as field1
, case when <condition 1>
  then '2'
  end as field2

Or does this 2nd field need to be handled in a completely different if-then statement? Any advice on how to handle this scenario? I am very new to PL/SQL.

Upvotes: 0

Views: 405

Answers (2)

Ashish sinha
Ashish sinha

Reputation: 148

SELECT to_number(to_char(marriage_date, 'YYYY')) YEAR,

count(id_marriage) NUMBER_OF_MARRIAGES,

        sum(CASE WHEN REASON_OF_EXPIRATION != 'Death' THEN 1

                 ELSE 0

       end  ) NUMBER_OF_DIVORCES,

sum(case when REASON_OF_EXPIRATION = 'Death' THEN 1

else 0

end) NUMBER_OF_Deaths

FROM marriage GROUP BY to_number(to_char(marriage_date, 'YYYY')); the 2nd way is correct please find a sample code .

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

In order to create two different variables, you will need to repeat the case statement.

Your second formulation with two case statements is the right way to go.

Upvotes: 2

Related Questions