Re: Case Statement

From: ExecMan <artmerar_at_yahoo.com>
Date: Thu, 29 Sep 2011 13:44:18 -0700 (PDT)
Message-ID: <fe435726-3442-4145-abf0-0a11b8491593_at_x19g2000vbl.googlegroups.com>

On Sep 29, 2:58 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> On 29.09.2011 19:56, ExecMan wrote:
>
>
>
>
>
>
>
>
>
> > This is interesting, anyone ever get this?
>
> > Here is my calling line:  exec customer_report(12345, '', 12345)
>
> > PROCEDURE customer_report (p_customer_id NUMBER, p_adid VARCHAR2,
> > p_product_id NUMBER) IS
>
> > This fails the WHEN and executes the ELSE
> >    v_adid    := CASE p_adid
> >                   WHEN NULL THEN ' '
> >                   ELSE ' AND UPPER(ss.adid) = UPPER(''' || p_adid ||
> > ''')'
> >                 END;
>
> > This works fine and executes the proper IF side.
>
> > IF p_adid IS NULL THEN
> >    v_adid := ' ';
> > ELSE
> >    v_adid := ' AND UPPER(ss.adid) = UPPER(''' || p_adid || ''')';
> > END IF;
>
> > What is the difference?
>
>  From the docs:http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundam...
>
> <quote>
> If the expression in a simple CASE statement or CASE expression yields
> NULL, it cannot be matched by using WHEN NULL. In this case, you would
> need to use the searched case syntax and test WHEN expression IS NULL
> </quote>
>
> Applied to your example, you can change the simple case statement to
> searched case statement like this:
>
>      v_adid    := CASE
>                     WHEN p_adid is NULL THEN ' '
>                     ELSE ' AND UPPER(ss.adid) = UPPER(''' || p_adid ||
>   ''')'
>                   END;
>
> Best regards
>
> Maxim

Many thanks.

Can you do multiple statements under the WHEN? This does not work:

  CASE
    WHEN p_adid IS NULL THEN

      v_adid  := ' '
      v1_adid := ' '
    ELSE
     v_adid  := ' AND UPPER(ss.adid) = UPPER(''' || p_adid || ''')'
     v1_adid := ' AND UPPER(ss1.adid) = UPPER(''' || p_adid || ''')'

    END; It does not work if I put semi-colons at the end of the statements either. Received on Thu Sep 29 2011 - 22:44:18 CEST