Oracle视图使用Case when条件无法正常工作

Question

对于M_DESCR字段，我试图在altype = 'Cash total'时将值设置为'C'，在altype = 'Other total'时，我希望值为'O'，否则应为null。当我在第一个union all条件中尝试做同样的操作时，字段M_DESCR没有任何更改。

此外，我想在完成所有计算后从视图中删除字段ALTYPE，但不知道如何做到这一点。

Answer 1

据我所知，你可能正在寻找类似的东西，(唯一我对你提供的数据集有点怀疑，但可能我错了(

CREATE OR REPLACE VIEW
IS_ID
(
IDENTIFIER,
IMPORT_DATE,
EFFECTIVE_DATE,      
FUND_QUOTE,
FUND_QUOTE_CRNY,
FUND_QUOTE_DATE,
MEMBER_IDENTIFIER,        
MEMBER_QUOTE_CRNY,
MEMBER_QTY,
MEMBER_QTY_TYPE,
FORCE_FLAG,
MEMBER_DESCR,
MEMBER_RATIO,
MEMBER_MARKETVALUE
) AS
SELECT
IDENTIFIER,
IMPORT_DATE,
EFFECTIVE_DATE,      
FUND_QUOTE,
FUND_QUOTE_CRNY,
FUND_QUOTE_DATE,
MEMBER_IDENTIFIER,
MEMBER_QUOTE_CRNY,
MEMBER_QTY,
MEMBER_QTY_TYPE,
FORCE_FLAG,
CASE 
WHEN allocationassettype = 'Cash total'
THEN
'C'
WHEN allocationassettype = 'Other total'
THEN
'O'
ELSE NULL
END AS MEMBER_DESCR,
MEMBER_RATIO,
MEMBER_MARKETVALUE        
FROM
(
SELECT
IDENTIFIER,
IMPORT_DATE,
EFFECTIVE_DATE,      
FUND_QUOTE,
FUND_QUOTE_CRNY,
FUND_QUOTE_DATE,
MEMBER_IDENTIFIER,
MEMBER_QUOTE_CRNY,
MEMBER_QTY,
MEMBER_QTY_TYPE,
FORCE_FLAG,
MEMBER_DESCR,
MEMBER_RATIO,
MEMBER_MARKETVALUE,        
ALLOCATIONASSETTYPE
FROM
IS_TEST
WHERE
ALLOCATIONASSETTYPE != 'Cash'
and MEMBER_IDENTIFIER is not null
UNION ALL
SELECT
IDENTIFIER,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SUM(
CASE
WHEN allocationassettype = 'Cash'
THEN member_ratio
ELSE 0
END),
SUM(
CASE
WHEN allocationassettype = 'Cash'
THEN member_marketvalue
ELSE 0
END),
'Cash total'    
FROM
IS_TEST
GROUP BY
IDENTIFIER, MEMBER_DESCR
UNION ALL
SELECT
IDENTIFIER,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
100 - SUM(
CASE
WHEN MEMBER_IDENTIFIER IS NOT NULL
THEN member_ratio
ELSE 0
END) - SUM(
CASE
WHEN allocationassettype = 'Cash'
THEN member_ratio
ELSE 0
END),
SUM(member_marketvalue) - SUM(
CASE
WHEN allocationassettype = 'Cash'
THEN member_marketvalue
ELSE 0
END) - SUM(
CASE
WHEN MEMBER_IDENTIFIER IS NOT NULL
THEN member_marketvalue
ELSE 0
END),
'Other total'    
FROM
IS_TEST
GROUP BY
IDENTIFIER
);

Answer 2

我认为创建的视图没有任何问题。您可以简单地从选择列表和查看列列表中删除ALLOCATIONASSETTYPE列。

在测试用例中，我看不到您所描述的任何值：

**

allocationassettype='现金总额'，当allocationassertype='其他总计

**