CODEWITHSUNDEEP
sqloracle-database
Taku_
Taku_

Reputation: 1625

Improving Oracle Where Clause with equals or in values

Hello I am trying select based on a criteria for one of our datablocks. they have the option of an -All- choice and I am having trouble getting it to work or operate better.

How I have it now is this but it seems really clunky and I think sometimes it doesnt pick up some of the values

where SLRRDEF_RDEF_CODE = (select CASE
                    WHEN :parm_dd_rdef.RDEF_DESC like '-All-' Then 'A100'
                    WHEN :parm_dd_rdef.RDEF_DESC like '-All-' Then 'A110'
                    WHEN :parm_dd_rdef.RDEF_DESC like '-All-' Then 'B100'
                    WHEN :parm_dd_rdef.RDEF_DESC like '-All-' Then 'B110'
                    WHEN :parm_dd_rdef.RDEF_DESC like '-All-' Then 'B120'
                    WHEN :parm_dd_rdef.RDEF_DESC like '-All-' Then 'B130'
                    WHEN :parm_dd_rdef.RDEF_DESC like '-All-' Then 'W110'
                    WHEN :parm_dd_rdef.RDEF_DESC like '-All-' Then 'W590'
                    ELSE :parm_dd_rdef.RDEF_CODE
                    END from dual

I would like to try something for efficient like this but I get getting missing parenthesis/keyword errors and I dont believe that has anything to do with it. Would anyone able to help

where (CASE
      WHEN :parm_dd_rdef.RDEF_DESC like '-All-' THEN SLRRDEF_RDEF_CODE in ('A100','A110','B100','B110','B120','B130','W110','W590')
      WHEN :parm_dd_rdef.RDEF_DESC not like '-All-' THEN SLRRDEF_RDEF_CODE = :parm_dd_rdef.RDEF_CODE
      END)
      AND

where SLRRDEF_RDEF_CODE in (CASE WHEN :parm_dd_rdef.RDEF_DESC like '-All-' 
THEN ('A100','A110','B100','B110','B120','B130','W110','W590')
ELSE :parm_dd_rdef.RDEF_CODE)

Upvotes: 0

Views: 66

Answers (1)

Allan
Allan

Reputation: 17429

case is really intended to allow you to use Boolean logic in places where it is not normally allowed. Since the where clause is based on Boolean logic in the first place, using a case statement there is usually a misstep.

It's hard to tell exactly what you're trying to accomplish, but interpolating a bit, I think this may be the better solution to your scenario:

WHERE SLRRDEF_RDEF_CODE = :parm_dd_rdef.RDEF_CODE
      OR (:parm_dd_rdef.RDEF_DESC = '-All-' 
          and SLRRDEF_RDEF_CODE 
              in ('A100','A110','B100','B110','B120','B130','W110','W590'))

If you really don't care about the value of SLRRDEF_RDEF_CODE when the parameter is '-All-', then it's even easier:

where SLRRDEF_RDEF_CODE = :parm_dd_rdef.RDEF_CODE
       OR :parm_dd_rdef.RDEF_DESC = '-All-'

Upvotes: 3

Related Questions