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

Question

我知道我应该为所有列添加别名，我正在这样做，但我仍然遇到错误。

CREATE TABLE MENTIONS AS SELECT
    UM.USER_ID AS U_ID,
    UM.SCREEN_NAME AS USER_SCREEN_NAME,
    UM.MENTION_ID AS M_USER_ID,
    (
        SELECT
            UI.USER_SCREEN_NAME AS MENTIONED_USER
        FROM
            USER_INFO UI
        WHERE
            UI.USER_ID = UM.MENTION_ID
        AND ROWNUM = 1
    )
FROM
    USER_MENTION UM

USER_MENTION 表

 USER_ID       SCREEN_NAME    MENTION_ID
135846337   irisschrijft     774759032636727300
50117969    Chjulian         13769472
14411827    thenriques45     13769472
26681613    ahenotri        252074645
26681613    ahenotri         9796472
158378782   SpringerOpen    9796472
144241914   Kumarappan      252074645

用户信息表:

  USER_ID     USER_SCREEN_NAME
 22553325   jasonesummers   
23435691    QRJAM   false   
67421923    inTELEgentMSP   
97393397    knauer0x    
85303739    MarriageTheorem 
3842711     seki    
3036414608  Bayes_Rule  
838677852   BOLIGATOR   

我仍然收到上述错误，我做错了什么？

Answer 1

查找 Oracle Error Message Manual of the current Oracle version .这里提到了错误，但没有附加信息。

在这种情况下查找
Oracle Error Message Manual of version 9i
由于我不知道很多错误信息在 9i 手册中有描述，但在更高版本的手册中没有。 9i 是一个相当旧的版本，所以描述可能已经过时了。但它可能包含有值(value)的提示。

ORA-00998 must name this expression with a column alias
Cause: An expression or function was used in a CREATE VIEW statement, but no corresponding column name was specified. When expressions or functions are used in a view, all column names for the view must be explicitly specified in the CREATE VIEW statement.
Action: Enter a column name for each column in the view in parentheses after the view name.

我们没有 View ，而是一个由选择创建的表。实际上选择列表的最后一个表达式是一个没有别名的表达式。因此，尝试使用最后一个表达式的别名的语句。所以试试

CREATE TABLE MENTIONS AS SELECT
    UM.USER_ID AS U_ID,
    UM.SCREEN_NAME AS USER_SCREEN_NAME,
    UM.MENTION_ID AS M_USER_ID,
    (
        SELECT
            UI.USER_SCREEN_NAME
        FROM
            USER_INFO UI
        WHERE
            UI.USER_ID = UM.MENTION_ID
        AND ROWNUM = 1
    ) AS MENTIONED_USER
FROM
    USER_MENTION UM

内部选择列表中的列别名没有用，可以删除。