MOD command calling functions-CodePudding

I have two functions, which are working fine. I want to use the MOD or decode command to call the functions with different options but I can't seem to get the code below to work.

Below is my test CASE. Any help would be greatly appreciated. Thanks in advance for your time and expertise.


ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'

CREATE OR REPLACE FUNCTION random_timestamp(
      p_from      IN TIMESTAMP,
      p_to        IN TIMESTAMP,
      p_fraction  IN VARCHAR2   DEFAULT 'Y'
    ) RETURN TIMESTAMP
   IS
      return_val_y TIMESTAMP     := p_from   dbms_random.value () * (p_to - p_from   INTERVAL '1' DAY);
      return_val_n TIMESTAMP (0) := return_val_y;
 BEGIN
      RETURN  CASE
                    WHEN UPPER (SUBSTR (p_fraction, 1, 1)) = 'Y'
          THEN return_val_y
          ELSE return_val_N
          END;
END random_timestamp;
/

CREATE OR REPLACE FUNCTION random_interval(
  p_min      IN NUMBER,
  p_max      IN NUMBER,
  p_duration IN VARCHAR2,
  p_fraction  IN VARCHAR2   DEFAULT 'Y'
) RETURN INTERVAL DAY TO SECOND
IS
      return_val_y   INTERVAL DAY TO SECOND   := NUMTODSINTERVAL(DBMS_RANDOM.VALUE(p_min, p_max), p_duration);

   return_val_n   INTERVAL DAY TO SECOND   :=
( EXTRACT(DAY FROM return_val_y) * 24 * 60 * 60
           EXTRACT(HOUR FROM return_val_y) * 60 * 60
           EXTRACT(MINUTE FROM return_val_y) * 60
           FLOOR(EXTRACT(SECOND FROM return_val_y))
         ) * INTERVAL '1' SECOND;
BEGIN
      RETURN  CASE
                    WHEN UPPER (SUBSTR (p_fraction, 1, 1)) = 'Y'
          THEN return_val_y
          ELSE return_val_N
       END;
 END random_interval;
/

/* can't get this to work */

SELECT
    CASE MOD(LEVEL, 2)
     WHEN 0
     THEN   
         random_timestamp(TIMESTAMP '2022-04-01 00:00:00', TIMESTAMP '2022-04-30 00:00:00', 'Y') as ts,
         random_interval(1, 10, 'HOUR', 'Y') as invr
     ELSE 
         random_timestamp(TIMESTAMP '2022-04-01 00:00:00', TIMESTAMP '2022-04-30 00:00:00', 'N') as ts,
         random_interval(1, 10, 'HOUR', 'N') as invr
     END
FROM   dual
CONNECT BY level <= 10;

CodePudding user response：

A CASE expression returns a single expression; not multiple expressions. If you want multiple expressions then move the CASE from wrapping both of them (which is wrong) to inside each of the function calls:

SELECT random_timestamp(
         TIMESTAMP '2022-04-01 00:00:00',
         TIMESTAMP '2022-04-30 00:00:00',
         CASE MOD(LEVEL, 2) WHEN 0 THEN 'Y' ELSE 'N' END
       ) as ts,
       random_interval(
         1,
         10,
         'HOUR',
         CASE MOD(LEVEL, 2) WHEN 0 THEN 'Y' ELSE 'N' END
       ) as invr
FROM   dual
CONNECT BY level <= 10;

fiddle