Oracle sub error on query

Question

Following code I added to the SQL Server query and now have to do the same in Oracle. I need to do grouping in the view rather than in the C#. I get this error message:

ORA-01747 Invalid user.table.column or column specification.

How must I code this to work in Oracle?

SELECT CTE.FACILITY_KEY, CTE.DATE, CTE.PATIENT_STATUS, COUNT(*) AS [COUNT]
  FROM CTE
  GROUP BY CTE.FACILITY_KEY, CTE.DATE, CTE.PATIENT_STATUS;

at the beginning of query I have this full code here:

  CREATE OR REPLACE VIEW DBD_V_CDL_CHANGES AS 
WITH CTE AS
(
SELECT TR.FACILITY_KEY
       , MV.VALUE_CODE
       , CAST(COUNT(*) AS NUMERIC(9, 0)) COUNT
    FROM OPTC.THS_T_TRANSACTIONS1 TR
    JOIN OPTC.THS_M_MENU2 M
      ON M.MENU_ID = TR.MENU_ID
    JOIN OPTC.THS_M_VALUES MV
      ON MV.MENU_ID = TR.MENU_ID_VALUE
    JOIN OPTC.THS_M_VALUES MV2
      ON MV2.MENU_ID = TR.PREVIOUS_MENU_ID_VALUE
    JOIN OGEN.GEN_M_PATIENT_MAST PM
      ON PM.PAT_NUMBER = TR.PAT_NUMBER
   WHERE TR.TR_DATETIME BETWEEN TRUNC(SYSDATE)
                            AND TRUNC(SYSDATE) + 86399 / 86400
     AND TR.EDIT_NO < 0
     AND MV.VALUE_TYPE IS NULL
     AND MV2.VALUE_TYPE IS NULL
     AND MV.VALUE_CODE >= 0
     AND MV2.VALUE_CODE >= 0
     AND M.SUB_SYS_EXT = 'G1'
     AND ABS(MV.VALUE_CODE - MV2.VALUE_CODE) > 1
     AND (PM.DISCHARGE_DATE IS NULL OR PM.DISCHARGE_DATE < SYSDATE)
   GROUP BY TR.FACILITY_KEY, MV.VALUE_CODE)

   SELECT CTE.FACILITY_KEY, CTE.DATE, CTE.PATIENT_STATUS, COUNT(*) AS [COUNT] FROM CTE
  GROUP BY CTE.FACILITY_KEY, CTE.DATE, CTE.PATIENT_STATUS;

Answer 1

I see a few things wrong with your code.

First, you are selecting the following three columns FACILITY_KEY, VALUE_CODE and the count in the CTE:

SELECT TR.FACILITY_KEY , 
    MV.VALUE_CODE , 
    COUNT(*) as Count -- note there is no need to CAST(COUNT(*) AS NUMERIC(9, 0)) this
FROM OPTC.THS_T_TRANSACTIONS1 TR 

But then when you select from the CTE you are selecting columns that you are not returning in the CTE:

with cte as
( 
  -- your query here does not return DATE or PATIENT_STATUS
) 
SELECT CTE.FACILITY_KEY, 
    CTE.DATE, 
    CTE.PATIENT_STATUS, 
    COUNT(*) AS COUNT 
FROM CTE 
GROUP BY CTE.FACILITY_KEY, CTE.DATE, CTE.PATIENT_STATUS;

Where do PATIENT_STATUS and Date come from since you are not including them in your CTE? So these do not exist when you are trying to select them.

I replicated your error by including columns in the list that were not select in the CTE query.

The second issue is the CTE.DATE column. DATE is a reserved word, place that is double quotes CTE."DATE"

Answer 2

...AS [COUNT], ...AS NUMERIC(9, 0)) is not Oracle syntax and will never work. Simply remove [ ] and use NUMBER instead of NUMERIC. There is no need to CAST Count(). The Count() function will always return number, e.g. 0-zero or some number.

This is valid syntax in Oracle:

SELECT deptno, count(*) total_count_by_dept -- no need to cast or AS --
  FROM scott.emp
GROUP BY deptno
/

Try not to use reserved words as COUNT for aliases:

SELECT CTE.FACILITY_KEY, CTE.DATE, CTE.PATIENT_STATUS, COUNT(*) AS total_cnt -- 'AS' is for clarity only, not required
  FROM CTE
 GROUP BY CTE.FACILITY_KEY, CTE.DATE, CTE.PATIENT_STATUS
/