Oracle View not working correctly using Case when condition

Question

For M_DESCR field I am trying to put the value as 'C' when altype = 'Cash total', when altype = 'Other total' then I want value as 'O' else it should be null. When I am trying to do the same in my first union all condition then nothing has been changed for field M_DESCR.

Also I want to remove field ALTYPE from view at the end when all the calculation has been done but don't know how to do that.

Answer 1

With what I understood may be you are looking for something like, ( Only thing I am little skeptical with the data set you provided but may be I am wrong)

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
);