ORA-00998: "must name this expression with a column alias"-CodePudding

I need help with that error this is my code

CREATE OR REPLACE VIEW emplpersuc
AS SELECT SUCURSAL.NOMBRE, COUNT(SUCURSAL.NOMBRE) FROM VENTA
JOIN SUCURSAL ON SUCURSAL_CODIGO_SUCURSAL=CODIGO_SUCURSAL
GROUP BY SUCURSAL.NOMBRE
ORDER BY COUNT(SUCURSAL.NOMBRE) DESC;

I tried everything in my knowledge, i´m desperate

CodePudding user response：

error message says you are trying to use an expression or a function in your SELECT clause without giving it a column alias.

Change with this :

SELECT SUCURSAL.NOMBRE, COUNT(SUCURSAL.NOMBRE) AS count_sucursal

CodePudding user response：

As the error states, you need to make sure your columns all have valid identifiers (and although COUNT(NOMBRE) is a valid expression, it is not a valid identifier to name the column in the view).

You can either name it in the view's signature:

CREATE OR REPLACE VIEW emplpersuc (nombre, nombre_count) AS
SELECT s.NOMBRE,
       COUNT(s.NOMBRE)
FROM   VENTA v
       JOIN SUCURSAL s
       ON SUCURSAL_CODIGO_SUCURSAL=CODIGO_SUCURSAL
GROUP BY s.NOMBRE
ORDER BY COUNT(s.NOMBRE) DESC;

or with a column alias in the query:

CREATE OR REPLACE VIEW emplpersuc AS
SELECT s.NOMBRE,
       COUNT(s.NOMBRE) AS nombre_count
FROM   VENTA v
       JOIN SUCURSAL s
       ON SUCURSAL_CODIGO_SUCURSAL=CODIGO_SUCURSAL
GROUP BY s.NOMBRE
ORDER BY COUNT(s.NOMBRE) DESC;