decode statement in oracle

select ri.customer_id,c.name,c.ar_account,inv_product_type,
       ri.unit_id,substr(ri.product_code,1,5) product_code,
       mc.description,
         ri.return_reason_code,
         ri.exp_date,trunc(return_date) return_date
  from return_advices@plab.world ra,
       return_items@plab.world ri,
         valid_products@plab.world vp,
         customers@plab.world c,
         units@plab.world u,
         master_codes mc
 where ri.return_advice_id = ra.return_advice_id
   and vp.product_code = substr(ri.product_code,1,5)
   and c.customer_id = ri.customer_id
   and u.unit_id = ri.unit_id
   and u.system_label_decision = substr(mc.udf1, 1,2)
   and mc.term_date is null
   and ri.move_date between '01-may-2013' and '02-may-2013'  
and decode(:p_inv_type
 order by c.ar_account,return_date

-------------------------I need help in the decode statement

If :p_inv_type = 'LPHER' then get vp.inv_product_type = 'LPHER'
elsif p_inv_type  = 'RBC LRBC'   then

I want vp.inv_product_type in ('RBC','LRBC')

I know I have asked these many times, but this is a little different.
LVL 6
anumosesAsked:
Who is Participating?
 
sdstuberCommented:
don't use decode (or case)  put the conditions directly into the where clause

and (
    (p_inv_type = 'LPHER' and vp.inv_product_type = 'LPHER')
   or
    (p_inv_type  = 'RBC LRBC'  and vp.inv_product_type in ('RBC','LRBC'))
)


using decode on the column prevents the optimizer from considering all options,
most notably indexes  but it also skews the costing algorithms
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.