create view with union all using select not working

Question

I have below query where i am getting error as ORA-00979: not a GROUP BY expression and also logically its not working.

Normally the logic should be for every FUND_ISIN check the value for member_descr. If for FUND_ISIN the member_descr != 'O' or member_descr is null then take all the rows from IS_ID table. For every FUND_ISIN if the member_descr = 'O' then do calculation for member_ratio fields value such as 100-sum(all the values for member_ratio where member_descr!='O').

For example in this case the expected output will be like below. We can see below for FUND_ISIN L000123 we check for member_descr = 'O' and then did calculation as 100-(8.5643 + 6.94816) = 84,48754 and for other rows where member_descr!= 'O' we just take those rows. In this way we have to do for every fund_isin.

Output expected:

FUND_ISIN  FUND_QUOTE_CRNY  MEMBER_DESCR  MEMBER_RATIO  ALLOCATIONASSETTYPE
L000123       USD               O           84,48754       Other total
L000123       USD               null        8.5643         Cash total
L000123       USD               null        6.94816        member

Below is fiddle which is currently throwing not a group by expression error: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=3f48a9a4f439869168775bb2c7283db6

Answer 1

I think you may be over complicating your query. There is no need for a UNION ALL or even a GROUP BY to achieve the desired output. You can use analytic functions to achieve your result.

Query

SELECT fund_isin,
       fund_quote_crny,
       member_descr,
       CASE member_descr
           WHEN 'O'
           THEN
                 100
               - SUM (CASE member_descr WHEN 'O' THEN 0 ELSE member_ratio END)
                     OVER (PARTITION BY fund_isin)
           ELSE
               member_ratio
       END    AS member_ratio,
       allocationassettype
  FROM is_id;

Result

   FUND_ISIN    FUND_QUOTE_CRNY    MEMBER_DESCR    MEMBER_RATIO    ALLOCATIONASSETTYPE
____________ __________________ _______________ _______________ ______________________
L000123      USD                O                      84.48754 Other total
L000123      USD                null                    6.94816 member
L000123      USD                null                     8.5643 Cash total