sql - "ORA-00998: must name this expression with a column alias" i am having this error can anyone tell me what's the problem and its solution

Question

this is my sample code;-

CREATE TABLE orders
(

ord_no int,
purch_amt float,
ord_date varchar(50),
customer_id int,
salesman_id int,
PRIMARY KEY(ord_no)
);

INSERT INTO orders (ord_no, purch_amt, ord_date, customer_id, salesman_id)
VALUES (70001, 150.5, '2012-10-05', 3005, 5002);

CREATE VIEW totalforday 
 AS SELECT ord_date , COUNT(DISTINCT customer_id),
 AVG(purch_amt), SUM(purch_amt)
 FROM orders
 GROUP BY ord_date;

标签： sqloracle

Answer 1

想象一下为您的新视图编写查询。

SELECT ord_date,
       COUNT(DISTINCT customer_id),
       AVG(purch_amt),
       SUM(purch_amt)
 FROM totalforday;

您的列名 - 您是在要求一个名为 SUM(purch_amt) 的列，还是要对一个名为 purch_amt 的列进行求和？

您需要为视图提供有效的列名，无论是隐式还是显式。添加别名可以让数据库隐含地找出为您的列命名的内容。

像这样。

CREATE or replace VIEW totalforday 
 AS SELECT ord_date , COUNT(DISTINCT customer_id) how_many_customers,
 AVG(purch_amt) avg_amt, SUM(purch_amt) total_amt
 FROM orders
 GROUP BY ord_date;