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?

ExecMan <artmerar (AT) yahoo (DOT) com> wrote:
I'd tend to believe that CASE makes comparisons, and thus your
CASE statement was equivalent to "IF p_adid = NULL THEN ..."
(with "=" not "IS")

On 29.09.2011 19:56, ExecMan wrote:
From the docs:
http://download.oracle.com/docs/cd/B...s.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

On Sep 29, 2:58*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
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.

On 09/29/2011 10:44 PM, ExecMan wrote:
Because CASE is an expression and allows just one expression per case.
Btw, you find that in the docs as well
http://download.oracle.com/docs/cd/E...4.htm#i1033392

Congratulations: you are just learning the difference between a
statement and an expression!

Cheers

robert