CODE THE DECODE FUNCTION?

[jbpelletier]

hi,
im trying to recode the DECODE function for the PLSQL part (since i can't called DECODE in PLSQL)

My problem : i dont know with what i should replace TYPE_TO_USE? by, i whant it to be of any type.

FUNCTION DECODE (in_bool_cond BOOLEAN,in_true TYPE_TO_USE?,in_false TYPE_TO_USE?,) RETURN TYPE_TO_USE?,
IS
BEGIN
IF in_bool_cond THEN
return in_true;
ELSE
return in_false;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

tanx jb

 

[SantaMufasa]

JB,

I'm certain we can help you, but I'm not quite clear on what you are trying to do. What is you final objective/ultimate capability for which you are looking? If you are trying to simulate the "DECODE" function in PL/SQL, there are cleaner ways to make that happen.

Please advise with perhaps an example with real data.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:51 (03Dec04) UTC (aka "GMT" and "Zulu"),
@ 11:51 (03Dec04) Mountain Time

 

[jbpelletier]

Hi,
In fact its not really the decode function..(sorry im new)
it looks more like the
"paradox IIF function"
"access IIF function"
"C++ (cond?when_true:when_false); function"

my fonction should accept a boolean result expression
and return the "when true" part if the cond is true
else it should return de "when false" part.

(NOTE:i would call it IIF instead of decode)
Ex:
MyStr := IIF(1+1=2,'1+1=2','1+1<>2'); --MyStr = '1+1=2'
MyInt := IIF(NVL(MyIntField,0)=0,-1,MyInt*MyInt); --Return the square of MyIntField if not null and <> 0, else return -1

Maybe there is already a function that do that ???
if not .. what should be the datatype of the "when true", "when false" and the function result?

tanx
jb

 

[SantaMufasa]

JB,

I don't know if this is what you want, but here are 1) a working "bool" function, 2) a sample "bool" test script (which you should save to a file then invoke as a script), and 3) a sample invocation of the test script with its output:

Section 1 -- "Bool" function:

create or replace function bool (x boolean,arg1 varchar2, arg2 varchar2)
    return varchar2 is
begin
    if x then return arg1;
    else return arg2;
    end if;
end;
/

Function created.

Section 2 -- Sample "bool" test script:

accept m prompt "Enter a value for m: "
set serveroutput on
declare
    y boolean;
begin
    y := &m > 0;
    dbms_output.put_line(bool(y,'&m > 0','&m <= 0'));
end;
/

Section 3 -- Sample invocation of the test script with its output:

@tt_183
Enter a value for m: 10
10 > 0

@tt_183
Enter a value for m: 0
0 <= 0

@tt_183
Enter a value for m: -15
-15 <= 0

Simple-minded test, but it's a proof-of-concept for you. Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:37 (06Dec04) UTC (aka "GMT" and "Zulu"),
@ 15:37 (06Dec04) Mountain Time

 

[jbpelletier]

hi and tanx for your help,

its not exacly in the algorithm that i have problem but with the datatype to use,

As u know if i call the function "DECODE" in sql it can accept almost all datatype..

ex: DECODE(MYFIELD,null,1,'HI');

But coding
function bool (x boolean,arg1 varchar2, arg2 varchar2)
return varchar2
Will make "bool" to only accept arg1 and args2 as VARCHAR2 and i dont whant to have to use a TO_CHAR in my function call..

i whant arg1 and arg2 to be of any possible know datatype (at least those that oracle consider as primitive) and the same for the function return result

jb

 

[carp]

And it can do so through overloading. I believe what you want to do is create a package. The package will have multiple IIF functions, one for each combination of datatypes you want to work with.

Take a look at the source code for the STANDARD package specification and you'll see what I mean. Part of the decode definition looks like:

 RES                            VARCHAR2                IN
FUNCTION DECODE RETURNS DATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EXPR                           NUMBER                  IN
 PAT                            NUMBER                  IN
 RES                            DATE                    IN
FUNCTION DECODE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EXPR                           VARCHAR2                IN
 PAT                            VARCHAR2                IN
 RES                            NUMBER                  IN
FUNCTION DECODE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EXPR                           VARCHAR2                IN
 PAT                            VARCHAR2                IN
 RES                            VARCHAR2                IN
FUNCTION DECODE RETURNS DATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EXPR                           VARCHAR2                IN
 PAT                            VARCHAR2                IN
 RES                            DATE                    IN
FUNCTION DECODE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EXPR                           DATE                    IN
 PAT                            DATE                    IN
 RES                            NUMBER                  IN
FUNCTION DECODE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EXPR                           DATE                    IN
 PAT                            DATE                    IN
 RES                            VARCHAR2                IN
FUNCTION DECODE RETURNS DATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EXPR                           DATE                    IN
 PAT                            DATE                    IN
 RES                            DATE                    IN

When you run DECODE, Oracle matches the signature of your call to the appropriate function signature to select the code that can handle your inputs.

 

[jbpelletier]

i know i can have multi declaration of a function in a package but, its look that i can't in the public function part of a view

(function name already in use)

im not looking for a "walk around" (actualy i do exacly what u point me) but for a real "anytype" datatype

maybe i can't simply not do what i whant

jb

 

[carp]

You can use VARCHAR2 for just about anything you are likely to want to do. However, the problem then becomes your code has to sort out what you have REALLY passed in. For instance, if you pass in "2" as a value, do you mean the number 2 or the character '2'? How will your code determine this? That is why I suggested a package full of overloaded functions.
So iif_pkg would might have the following declarations:
PACKAGE iif_pkg IS
FUNCTION iif(p1 number, p2 number) RETURN number;
FUNCTION iif(p1 date, p2 date) RETURN date;
etc
END;
and your call would look something like:

SELECT iif_pkg.iif(<number arg>,<number_arg>) from ....
or
SELECT iif_pkg.iif(<date arg>,<date_arg>) from ...
etc.

"i know i can have multi declaration of a function in a package but, its look that i can't in the public function part of a view" - I'm not sure what you mean by this.