Re: Case Statement

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/fundamentals.htm#BABIHIHF

<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 Received on Thu Sep 29 2011 - 21:58:44 CEST