Re: Case Statement
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 30 Sep 2011 20:27:37 +0200
Message-ID: <4E860A19.90706_at_googlemail.com>
>> 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
Date: Fri, 30 Sep 2011 20:27:37 +0200
Message-ID: <4E860A19.90706_at_googlemail.com>
On 09/29/2011 10:44 PM, ExecMan wrote:
> 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.
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/E11882_01/server.112/e17118/expressions004.htm#i1033392
Congratulations: you are just learning the difference between a statement and an expression!
Cheers
robert Received on Fri Sep 30 2011 - 20:27:37 CEST