CREATE OR REPLACE FUNCTION sqlif (
--============================================================================
-- Copywright 2002, joel crainshaw & chet  west
--============================================================================
-- NAME:       sqlif
-- PURPOSE:    simple if-then-else functioon for use in SQL
--
-- NOTES:     PARAMETERS:
--               p_left = left compare vallue
--               p_oper = compare operatorr
--               p_right = right compare vvalue
--               p_then = result of true ooutcome
--               p_else = result of false  outcome
--               p_right1 = right-side of  between comparison
--
--
-- MODIFICATION HISTORY
-- PERSON       DATE       COMMENTS
-- ----------   ---------- -------------------------------------------
-- joel		7/25/02    initial development
--============================================================================
   p_left VARCHAR2 DEFAULT '0'
  ,p_oper VARCHAR2 DEFAULT '='
  ,p_right VARCHAR2 DEFAULT '1'
  ,p_then VARCHAR2 DEFAULT NULL
  ,p_else VARCHAR2 DEFAULT NULL
  ,p_right1 VARCHAR2 DEFAULT NULL
)
   RETURN VARCHAR2 IS
   v_oper VARCHAR2 (80) := SUBSTR (UPPER (p_oper), 1, 5);
BEGIN
   IF    (    v_oper IN ('=', 'E', 'EQ', 'EQU', 'EQUAL')
          AND p_left = p_right
         )
      OR (    v_oper IN ('<', 'L', 'LT', 'LESS', 'BELOW', 'UNDER')
          AND p_left < p_right
         )
      OR (    v_oper IN ('>', 'G', 'GT', 'GR', 'GRT', 'GREAT', 'ABOVE', 'OVER')
          AND p_left > p_right
         )
      OR (    v_oper IN ('<=', 'LE', 'LTE', 'LTEQ', 'LTOEQ', 'LEEQU')
          AND p_left <= p_right
         )
      OR (    v_oper IN ('>=', 'GE', 'GTE', 'GTEQ', 'GTOEQ', 'GRTEQ')
          AND p_left >= p_right
         )
      OR (    v_oper IN ('!=', '<>', 'N', 'NE', 'NOT', 'NOTEQ', 'NEQ', 'GTLT', 'LTGT')
          AND p_left != p_right
         )
      OR (    v_oper IN ('B', 'BET', 'BETWE')
          AND p_left BETWEEN p_right AND p_right1
         ) THEN
      RETURN p_then;
   ELSE
      RETURN p_else;
   END IF;
END;
/