Stack Exchange
i need help to remove a result in my query
I have this query
    Select   order_id 
        ,to_char(created_date + interval '7 hour','DD/MM/YYYY') As "Date"
        ,(Case When debitor_merchant_code <>'' then debitor_merchant_code
              When debitor_merchant_code ='' then creditor_merchant_code end) as "debitor_merchant_code" 
        ,trans_type_code
        ,sub_trans_type_code       
From orders
Where    service_type_code = 'WALLET'
     and status = '3'
     and trans_type_name <> 'Refund'        
Order by created_date DESC;

So i have this table full of result like this
Order_id        Date       debitor_merchant_code    trans_type_code    sub_trans_type_code
123          30/03/2022        MUATHE                PAY2MERCHANT            BUYCARD
456          30/03/2022          KB                  PAY2MERCHANT            BUYCARD
6789         30/03/2022         ABC                  PAY2MERCHANT            BUYCARD

But if i put a clause to remove the result i don't need after Where like this and Case when debitor_merchant_code = 'MUATHE' then sub_trans_type_code <> 'BUYCARD' end
The result only show debitor_merchant_code = 'MUATHE'
Anyone can help me fix this. I want all result but remove debitor_merchant_code = 'MUATHE' if sub_trans_type_code <> 'BUYCARD'

Could you provide some sample data and expect result?

ok i will edit my question

I'm not certain I understand the question, but I think you just want to add a where condition with some parentheses so it is evaluated together:

    Select   order_id 
        ,to_char(created_date + interval '7 hour','DD/MM/YYYY') As "Date"
        ,(Case When debitor_merchant_code <>'' then debitor_merchant_code
              When debitor_merchant_code ='' then creditor_merchant_code end) as "debitor_merchant_code" 
        ,trans_type_code
        ,sub_trans_type_code       
From orders
Where    service_type_code = 'WALLET'
     and status = '3'
     and trans_type_name <> 'Refund'
     and NOT ( debitor_merchant_code = 'MUATHE' AND sub_trans_type_code <> 'BUYCARD')
Order by created_date DESC;

The issue with your case statement is that for all cases when debitor_merchant_code <> 'MUATHE', it will return null.
