Re: BOOLEAN parameter passed to a stored function

Dear Spencer,

I have very similar thinking as yours, my replies are embedded:

On Sat, 2 Jun 2001 19:25:56 -0500, "Spencer" <spencerp_at_swbell.net> wrote:

>"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message
>news:4t9ihtcahls3dn9b1dnm1rprc9mojek7b0_at_4ax.com...
>> Dear all,
>>
>> The following function takes BOOLEAN parameters:
>>
>> CREATE OR REPLACE FUNCTION sf_case
>> (i_case1 IN BOOLEAN DEFAULT FALSE,
>> i_case2 IN BOOLEAN DEFAULT FALSE)
>> RETURN INTEGER
>> IS
>> v_result INTEGER;
>> BEGIN
>> IF i_case1 THEN
>> v_result := 1;
>> ELSIF i_case2 THEN
>> v_result := 2;
>> ELSE
>> v_result := 0;
>> END IF;
>> RETURN v_result;
>> END;
>> /
>>
>> However, when I try it in SQL*Plus, logical expressions cannot be
>> evaluated before passed into the stored function: (only literal TRUE
>> and FALSE will be accepted)
>>
>> SQL> SELECT SF_CASE(2>1,2>1) FROM DUAL;
>> SELECT SF_CASE(2>1,2>1) FROM DUAL
>> *
>> ERROR at line 1:
>> ORA-00907: missing right parenthesis
>>
>> Anyone can help? (Oracle 8.0.5) Thanks in advance.
>>
>> Dino
>>
>
>you would need another function that would accept the
>'2>1' as a VARCHAR2 and return a BOOLEAN. e.g.
>
>FUNCTION test_boolean
>(as_string IN VARCHAR2
>)RETURN BOOLEAN
>IS
>BEGIN
> IF as_string = '2>1' THEN
> RETURN FALSE;
> END IF;
> RETURN NULL;
>END test_boolean;
>
>and then use this function in your call:
>
>SELECT SF_CASE(test_boolean('2>1'),test_boolean('2>1')) FROM DUAL;
>
>i don't actually recommend that you do this, as i don't understand what it
>is
>you are trying to achieve. the SF_CASE function looks strange, bizarre...
>

The psodo-code of SF_CASE(case1,case2,...,etc.) RETURN NUMBER is: "if case1 then 1 elsif case2 then 2 ... else 0 end-if" so the '2>1' example expression above should somehow be evaluated to a BOOLEAN value by itself, it stands for a "logical expression", not "a value to be tested against some criterium". Unfortunately, SQL*PLUS doesn't seem to evaluate logical expressions which serve as actual paremeters.

>unfortunately, the CASE function was not introduced until after Oracle 8.0.
>a reasonable subset of the CASE type functionality can be achieved with
>the DECODE function...
>
>for example, let's assume that a,b,c and d each represent some expression
>of type NUMBER. the pseudo-code " if a>b then c else d end-if " can be
>easily implemented in SQL using a combination of the DECODE and SIGN
>functions, e.g.
>
>DECODE( SIGN( a - b ) , 1 , c , d )
>
>the pseudo-code "if a>b then 1 elsif c>d then 2 else 0 end-if" can be
>expressed using builtin functions:
>
>DECODE(SIGN(a-b),1,1,DECODE(SIGN(c-d),1,2,0))
>
>implementing "and" and "or" logic in a DECODE is also doable, but it
>can start to get really ugly...
>
>a better way to go, if you have complex logic to perform, is to code
>the logic as a PL/SQL function, and call the PL/SQL function from
>your SQL, e.g.
>
>FUNCTION my_gt_test
>(an_arg1 IN NUMBER
>,an_arg2 IN NUMBER
>,an_arg3 IN NUMBER
>,an_arg4 IN NUMBER
>)RETURN NUMBER
>IS
>BEGIN
> IF an_arg1 > an_arg2 THEN
> RETURN 1;
> ELSIF an_arg3 > an_arg4 THEN
> RETURN 2;
> END IF;
> RETURN 0;
>END my_gt_test;
>
>and use the function in your SQL:
>
>SELECT my_gt_test(2,1,2,1 ) FROM DUAL;
>
>HTH
>
>

Yes, the my_gt_test(margin1, margin2, ..., etc.) RETURN NUMBER is exactly what I am going to write instead of the more general sf_case function. Through this discussion, I will understand the limitations of SQL and PL/SQL languages.

Dino Received on Sat Jun 02 2001 - 19:42:16 CDT