Why is that a union of views works but when I create a view out of union of view, it fails

In ORACLE, I am trying to create a view which is a union of view:

CREATE VIEW "TEST_ALL"
AS 
( SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW1
UNION ALL
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW
UNION ALL
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW3
UNION ALL
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW4
UNION ALL
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW5
UNION ALL
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW6
)

This does not work? However when I remove M_ID, it creates the view.

Also when I just union all the inner views it works. Can someone please help?

In the query:

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,
       M_ID,
       COUNT(DISTINCT OBJECT_ID) as RESULT
FROM   VIEW1

Then:

• SYS_EXTRACT_UTC(SYSTIMESTAMP) is effectively constant for all the rows;
• COUNT(DISTINCT OBJECT_ID) is an aggregation; and
• M_ID is an un-aggregated column.

You are mixing aggregated and un-aggregated columns and do not have a GROUP BY clause.

Either change each sub-query to:

1. SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,
          M_ID,
          COUNT(DISTINCT OBJECT_ID) as RESULT
   FROM   VIEW1
   GROUP BY M_ID

2. Or use an analytic function to generate the count:

   SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,
          M_ID,
          COUNT(DISTINCT OBJECT_ID) OVER () as RESULT
   FROM   VIEW1

And repeat for each of the views you ar UNIONing together.