How to Use Case in Where Condition in Oracle?

See more:

Oracle

Hi All,

Can any one help me using Case in Where Condition in Oracle or modify the below query.

using below query i am getting single row instead of condition AND vt.ticket_type NOT IN('DEFAULT','HARD')

with out using AND vt.ticket_type NOT IN('DEFAULT','HARD') condition how can i get single row.

tbl_customers

pk_customer_id	customer name	Status
1	           Peter	Active
2	           Candy	Active

tbl_tickets

pk_ticket_id	ticket_type	Meaning
1	             EASY	Tk EASY
2	          Default	Tk Default
3	            HARD	Tk HARD

tbl_tickets_customers

Pk_tkt_Customer_id	fk_customer_id	Status
1	                       1	Active
2	                       2	Active

SELECT  cvt.pk_ticket_id
          , cvt.ticket_name
          , cvt.template_file_name AS template_name
          , (CASE WHEN cc.verified ='Y' THEN
             (SELECT decode(vt.ticket_type, 'EASY', 'Tk Easy')
              FROM tbl_tickets vt
                , tbl_tickets_customers vtc
                , tbl_customers cc
             WHERE vtc.fk_ticket_id = vt.pk_ticket_id
                  AND vtc.fk_tkt_customer_id = cc.pk_customer_id
                  AND cc.pk_customer_id = 1
                  AND vt.ticket_type NOT IN('DEFAULT','HARD')
                  AND vt.active_flag = 0
                  AND cc.active_flag = 0
                  AND vtc.active_flag = 0)
             ELSE
               decode(cvt.ticket_type, 'EASY', 'Tk Easy'
                                       , 'DEFAULT', 'Tk Default'
                                       , 'HARD', 'Tk Hard')

             END)AS ticket_type

    FROM    tbl_TICKETS_CUSTOMERS cvtc
          , tbl_TICKETS cvt
          , tbl_CUSTOMERS cc
    WHERE    cvtc.fk_ticket_id = cvt.pk_ticket_id
            AND cvtc.fk_tkt_customer_id = cc.pk_customer_id
            AND cc.pk_customer_id = 11
            AND vt.ticket_type NOT IN('DEFAULT','HARD');