对一个暗键使用两个查找函数
对一个暗键使用两个查找函数
提问于 2016-12-02 21:56:31
我已经为数据中可能发生的三种场景创建了三个查找函数。每个函数都应该返回一个account_dim_key，第一个场景从调用表中获取sr帐户ID和调用数据时间，如果调用日期在有效开始日期和结束日期之间，则返回一个小键；第二个场景使用调用日期(没有时间)，第三个场景只使用帐户id (没有日期)。如果第一个查找函数返回a-1，那么我想使用第二个查找函数，如果返回a-1，则使用第三个查找函数。我一直试图在case语句中使用它们，但是在我尝试过的所有场景中，它总是从第二个查找函数(在case语句的那个部分)返回dim键。在一个案例陈述中不可能这样使用三个函数，还是我完全错误地使用了它。
查找1
CREATE OR REPLACE FUNCTION LOOKUP_D_ACCT(p_acct_id IN VARCHAR2, p_date IN DATE)
RETURN NUMBER
PARALLEL_ENABLE
IS
v_dim_id NUMBER;
BEGIN
SELECT ACCOUNT_DIM_KEY
INTO v_dim_id
FROM
schema.D_ACCOUNT ac
WHERE p_acct_id = ac.ID
AND (p_date BETWEEN ac.EFFECTIVE_START_DT AND ac.EFFECTIVE_END_DT)
;
RETURN v_dim_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;
END LOOKUP_D_ACCT;
/查找2
CREATE OR REPLACE FUNCTION LOOKUP_D_ACCT_TRUNC_DT (p_acct_id IN VARCHAR2, p_date IN DATE)
RETURN NUMBER
PARALLEL_ENABLE
IS
v_dim_id NUMBER;
BEGIN
SELECT MAX(ACCOUNT_DIM_KEY)
INTO v_dim_id
FROM
schema.D_ACCOUNT ac
WHERE p_acct_id = ac.ID
AND p_date >= TRUNC(ac.EFFECTIVE_START_DT) AND p_date <= TRUNC(ac.EFFECTIVE_END_DT)
;
RETURN v_dim_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;
END;
/查找3
CREATE OR REPLACE FUNCTION LOOKUP_D_ACCT_NO_DT (p_acct_id IN VARCHAR2)
--LOOKUP FUNCTION TO PASS THROUGH FIELD_ALIGN_DIM_ID WITH DATE CRITERIA
--new one for practice load
--dated 8/31
RETURN NUMBER
PARALLEL_ENABLE
IS
v_dim_id NUMBER;
BEGIN
SELECT ACCOUNT_DIM_KEY
INTO v_dim_id
FROM
schema.D_ACCOUNT ac
WHERE p_acct_id = ac.ID
;
RETURN v_dim_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;
END;
/我的过程的选择如下：
SELECT cl.id,
cl.account_vod__c,
cl.call_date_vod__c,
cl.call_datetime_vod__c,
(CASE
WHEN NVL (
LOOKUP_D_ACCT(cl.ACCOUNT_VOD__C,
CL.CALL_DATETIME_VOD__C),
-1) = -1
--lookup 1
THEN
NVL (
LOOKUP_ACCT_TRUNC_DT (cl.ACCOUNT_VOD__C,
CL.CALL_DATE_VOD__C),
-1)
--lookup 2
WHEN NVL (
LOOKUP_ACCT_TRUNC_DT (cl.ACCOUNT_VOD__C,
CL.CALL_DATE_VOD__C),
-1) = -1
THEN
NVL (LOOKUP_D_ACCT_NO_DT (cl.ACCOUNT_VOD__C), -1)
--lookup 3
ELSE -3
END)
AS ACCOUNT_DIM_KEY,
LOOKUP_D_ACCT_VEEVA (cl.ACCOUNT_VOD__C, CL.CALL_DATETIME_VOD__C)
AS DTIME_DIM_KEY,
--lookup 1
LOOKUP_ACCOUNT_DIM_TEST (cl.ACCOUNT_VOD__C, CL.CALL_DATE_VOD__C)
AS DT_DIM_KEY,
--lookup 2
LOOKUP_D_ACCT_VEEVA_NO_DT (cl.ACCOUNT_VOD__C) AS NODT_DIM_KEY
--lookup 3
FROM schema.CALLTABLE CL
WHERE CL.ID IN ('a043600000Bija3AAB')我希望“ACCOUNT_DIM_KEY”字段始终使用查找1、2或3 在这里输入图像描述中的函数填充。
如能提供任何帮助，将不胜感激！
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-12-03 21:47:20
如果要传递条件的所有链，则不应使用case语句，而应串联检查每个条件：
if (NVL (LOOKUP_D_ACCT(cl.ACCOUNT_VOD__C, CL.CALL_DATETIME_VOD__C), -1) = -1) then
if (NVL ( LOOKUP_ACCT_TRUNC_DT (cl.ACCOUNT_VOD__C, CL.CALL_DATE_VOD__C), -1) = -1 ) then
if (NVL (LOOKUP_D_ACCT_NO_DT (cl.ACCOUNT_VOD__C), -1) = -1) then
-- if third func returned -1
else
-- action if third case
end if;
else
--action if second case
end if;
else
--action if first case
end if;UPD当然，如果它是SQL而不是PL/SQL，那么您应该使用嵌套case，就像前面说过的：
SELECT
(CASE
WHEN NVL (
LOOKUP_D_ACCT(cl.ACCOUNT_VOD__C,
CL.CALL_DATETIME_VOD__C),
-1) = -1
--lookup 1
THEN
CASE
WHEN
NVL (
LOOKUP_ACCT_TRUNC_DT (cl.ACCOUNT_VOD__C,
CL.CALL_DATE_VOD__C),
-1)= -1
THEN
NVL (LOOKUP_D_ACCT_NO_DT (cl.ACCOUNT_VOD__C), -1)
ELSE -3
END
WHEN NVL (
LOOKUP_ACCT_TRUNC_DT (cl.ACCOUNT_VOD__C,
CL.CALL_DATE_VOD__C),
-1) = -1
THEN
NVL (LOOKUP_D_ACCT_NO_DT (cl.ACCOUNT_VOD__C), -1)
--lookup 3
ELSE -3
END)
AS ACCOUNT_DIM_KEY,
LOOKUP_D_ACCT_VEEVA (cl.ACCOUNT_VOD__C, CL.CALL_DATETIME_VOD__C)
AS DTIME_DIM_KEY,
--lookup 1
LOOKUP_ACCOUNT_DIM_TEST (cl.ACCOUNT_VOD__C, CL.CALL_DATE_VOD__C)
AS DT_DIM_KEY,
--lookup 2
LOOKUP_D_ACCT_VEEVA_NO_DT (cl.ACCOUNT_VOD__C) AS NODT_DIM_KEY
--lookup 3
FROM schema.CALLTABLE CL
WHERE CL.ID IN ('a043600000Bija3AAB')页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40941448
复制相关文章
相似问题
腾讯云开发者
