Re: Can DECODE do this?

From: Garry M. Filimonov <garry_at_fly.triniti.troitsk.ru>
Date: 1996/08/23
Message-ID: <01bb90ca$a617ed00$bf0000c5_at_Garry.triniti.troitsk.ru>

John L Dunn <misioror_at_gas.uug.arizona.edu> wrote in article <Pine.SOL.3.91.960822131654.20260A-100000_at_helium.gas.uug.arizona.edu>...
> Greetings:
>
> I'm hoping someone can share their knowledge of DECODE with me.

---

DECODE is a way for break a head. Why ORACLE or ANSI don't include in standard SQL a functions like IIF(,,) or (?:) ???

Time at time the programing in DECODE born a huge monsters.

1. Using DECODE

 NOTE: DECODE don't support in PL/SQL. It's ONLY for SQL statments

Programing in DECODE have a few rules:

A<B	==>	Decode( Sign(A-B), -1, true_expr, false_expr )
A<=B	==>	Decode( Sign(A-B), 1, false_expr, true_expr )
A>B	==>	Decode( Sign(A-B), 1, true_expr, false_expr )
A>=B	==>	Decode( Sign(A-B), -1, false_expr, true_expr )
A=B	==>	Decode( A, B, true_expr, false_expr )

for using AND & OR replace above false_expr=0, true_expr=1

LogA  and  LogB 	==>	Decode( LogA * LogB, 0, false_expr, true_expr )
LogA  or  LogB		==>	Decode( LogA + LogB, 0, false_expr, true_expr )
LogA  xor  LogB		==>	Decode( LogA, LogB, false_expr, true_expr )
nor LogA		==>	Decode( LogA,  0, true_expr, false_expr )

==================================================


> In procedural code, I'd write something like


> 	if enroll_date<begin_date and term_date>end_date

> 	then total_days=end_date - start_date

==================================================

Decode( Decode(Sing(enroll_date-begin_date),-1,1,0)
            * Decode(Sign(term_date-end_date),1,1,0),
       0, total_days /*nothing*/, end_date-start_date)

Next conditions doing like it.

---

>
> else if enroll_date < begin_date and term_date < end_date
> then total_days=term_date - start_date
>
> else if enroll_date > begin_date and term_date > end_date
> then total_days=end_date - enroll_date
>
> else if enroll_date > begin_date and term_date < end_date
> then total_days=term_date - enroll_date
>
> Now, how can I do something like this using DECODE?

---

2. Second way for to resolve this problem is a creating PL/SQL package LOGICAL :  NOTE: This work in PL/SQL & SQL statments

CREATE OR REPLACE PACKAGE Logical AS
-- A < B for NUMBER, DATE, VARCHAR2
FUNCTION Lt( a NUMBER, b NUMBER ) RETURN NUMBER;

    PRAGMA RESTRICT_REFERENCES ( Lt, WNDS ); .... repeat for DATE and VARCHAR2
& repeat for Lq( A, B ) /* A<=B */, Gt( A, B ) /* A>B */, Gq( A, B ) /* A>=B */, Eq( A, B ) /* A=B */
-- AND
FUNCTION Land( a NUMBER, b NUMBER ) RETURN NUMBER;

    PRAGMA RESTRICT_REFERENCES ( Land, WNDS ); -- OR
FUNCTION Lor( a NUMBER, b NUMBER ) RETURN NUMBER;

    PRAGMA RESTRICT_REFERENCES ( Lor, WNDS ); -- OR
FUNCTION Lor( a NUMBER, b NUMBER ) RETURN NUMBER;

    PRAGMA RESTRICT_REFERENCES ( Lxor, WNDS ); -- NOT
FUNCTION Lnot( a NUMBER ) RETURN NUMBER;

    PRAGMA RESTRICT_REFERENCES ( Lnot, WNDS ); END Logical;

CREATE OR REPLACE PACKAGE BODY Logical AS -- A < B for NUMBER, DATE, VARCHAR2
FUNCTION Lt( a NUMBER, b NUMBER ) RETURN NUMBER IS  BEGIN IF a < b THEN RETURN 1; ELSE RETURN 0; END IF; END Lt; .... repeat for DATE and VARCHAR2
& repeat for Lq( A, B ) /* A<=B */, Gt( A, B ) /* A>B */, Gq( A, B ) /* A>=B */, Eq( A, B ) /* A=B */
-- AND
FUNCTION Land( a NUMBER, b NUMBER ) RETURN NUMBER  BEGIN IF a * b != 0 THEN RETURN 1; ELSE RETURN 0; END IF; END Land; -- OR
FUNCTION Lor( a NUMBER, b NUMBER ) RETURN NUMBER  BEGIN IF a + b != 0 THEN RETURN 1; ELSE RETURN 0; END IF; END Lor; -- XOR
FUNCTION Lxor( a NUMBER, b NUMBER ) RETURN NUMBER  BEGIN IF a != b THEN RETURN 1; ELSE RETURN 0; END IF; END Lxor; -- NOT
FUNCTION Lnot( a NUMBER ) RETURN NUMBER  BEGIN IF a = 0 THEN RETURN 1; ELSE RETURN 0; END IF; END Lnot; END Logical;

You can create no package but a lot functions for a lighting of applications.

3. Second way for to resolve this problem is a creating PL/SQL package LOGICAL :  NOTE: This work ONLY in PL/SQL, not for SQL statments

Create next function in package or out.

  function IIF(b BOOLEAN,e1 NUMBER,e2 NUMBER) return NUMBER IS    begin IF b THEN return e1; ELSE return e2; END IF; END;   function IIF(b BOOLEAN,e1 VARCHAR2,e2 VARCHAR2) return VARCHAR2 IS    begin IF b THEN return e1; ELSE return e2; END IF; END;   function IIF(b BOOLEAN,e1 DATE,e2 DATE) return DATE IS    begin IF b THEN return e1; ELSE return e2; END IF; END;

I had created it in a my first days of work with Forms.

---

Do You like it?

-- 
G'luck !
-----------------------------------------
Garry M. FIlimonov
LASU TRINITI, Troitsk, MO, Russia
garry_at_triniti.troitsk.ru

Received on Fri Aug 23 1996 - 00:00:00 CEST