CODEWITHSUNDEEP
sqldb2
Michal Palko
Michal Palko

Reputation: 661

SQL to detect if one column contains another column

I try to query as following:

   VOUCHER   TYPE    RESULT
   vchr1     REV     CONTAINS REV
   vchr1     REV     CONTAINS REV
   vchr1     COR     CONTAINS REV
   vchr2     COR     NOT CONTAINS REV
   vchr2     COR     NOT CONTAINS REV

I need to detect if voucher contains REV and give flag on row level. I tried OVER PARTITION but with no success. Any ideas?

Upvotes: 0

Views: 119

Answers (2)

nfgl
nfgl

Reputation: 3202

Yes you can do it with SUM() OVER()

select
  VOUCHER,
  TYPE,
  CASE SUM(CASE TYPE WHEN 'REV' THEN 1 ELSE 0 END) OVER(PARTITION BY VOUCHER)
    when 0 then 'NOT CONTAINS REV' else 'CONTAINS REV' end 
from tablename

Upvotes: 0

forpas
forpas

Reputation: 164089

Use a CASE expression and EXISTS:

select t.*,
  case when exists (select 1 from tablename where voucher = t.voucher and type = 'REV') 
       then 'CONTAINS REV' 
       else 'NOT CONTAINS REV' 
   end result
from tablename t

Upvotes: 1

Related Questions