sql - 我想将查询存储到新表中；怎么做？

Question

SQL> CREATE TABLE temp1
  2  AS
  3       SELECT deptno,
  4              job,
  5              SUM (sal),
  6              CASE GROUPING_ID (deptno, job)
  7                 WHEN 1 THEN 'dept_totalsal'
  8                 WHEN 2 THEN 'job_totalsal'
  9                 WHEN 3 THEN 'grandtotal'
 10              END
 11                 AS subtotal
 12         FROM emp e
 13     GROUP BY CUBE (deptno, job)
 14     ORDER BY deptno ASC;
            SUM (sal),
            *
ERROR at line 5:
ORA-00998: must name this expression with a column alias


SQL>

标签： sqloracleddl

Answer 1

您尚未指定列别名，SUM(SAL)但新表中的列需要名称。尝试给该表达式一个别名，例如SUM_SAL。

CREATE TABLE TEMP1
AS
SELECT DEPTNO,
       JOB,
       SUM(SAL) SUM_SAL,
       CASE GROUPING_ID(DEPTNO,
                        JOB)
         WHEN 1 THEN
           'dept_totalsal'
         WHEN 2 THEN
           'job_totalsal'
         WHEN 3 THEN
           'grandtotal'
       END SUBTOTAL
       FROM EMP E
       GROUP BY CUBE(DEPTNO,
                     JOB)
       ORDER BY DEPTNO ASC;