Re: CASE statement in WHERE Clause - Weird one

shortcutter_at_googlemail.com says...
>
> On 22.05.2014 09:21, Kay Kanekowski wrote:
> > Hi,
> > what about this ?
> > SELECT ROUND(AVG(pct_total),2)
> > INTO v_average
> > FROM pfp_to_buyer_agg
> > WHERE registration_period >= '01-OCT-2012'
> > AND ( ( v_period <> 1 and period = v_period)
> > or ( v_period = 1 and period in (0,1)
> > ) ;
> >
> > But as Geoff said, it's clearer to do the case outside the selects
>
> Why even bother to use CASE if there are only two alternatives? A good
> old if then else would do as well. After all this is about PL/SQL.

Why I use CASE over if-then-else:

CASE can be used in simple SQL as well as PL/SQL, whereas if-then-else is PL/SQL specific, and even though the OP's question was about PL/SQL, I prefer to standardise on one syntax option wherever possible.

A CASE statement can be easily expanded to cater for future additional conditions, the ELSIF alternative requires extra coding text and nested if-then-else constructs are a PITA.

I personally prefer to use the CASE_NOT_FOUND exception handler for unmatched conditions, putting all my exceptions in one part of the block rather than having them interspersed in the code.

I am unaware of any performance differential, but would be happy to be corrected by those more expert on this.

GM Received on Fri May 23 2014 - 01:26:13 CEST