I try to query as following:
x
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?
Advertisement
Answer
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