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;
1条答案
按热度按时间yhived7q1#
CASE
表达式返回单个表达式;不是多个表达式。如果您想要多个表达式,请将CASE
从 Package 两个表达式(这是错误的)移动到每个函数调用内部:fiddle