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

Question

I get that I should add alias with all the columns and I'm doing so but I'm still getting error.

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 table

 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_INFO table:

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

I'm still getting the above mentioned error, what am I doing wrong?

Answer 1

Lookup the Oracle Error Message Manual of the current Oracle version. Here the error is mentioned but without additional information.

In such a case look up the Oracle Error Message Manual of version 9i For reasons I don't know a lot of error messages have a description in the 9i manual but not in the manuals of higher versions. 9i is a rather old version so the description may be out of date. But it may contain valuable hints.

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.

We don't have a view but a a table that was created by a select. And actually the last expression of the select list is an expression without an alias. So try your statement using an alias for the last expression. So try

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

The column alias in the inner select list is useless and can be removed.

Answer 2

The problem with your query is that each column in the create table needs to have a name. You think you are assigning a name in the sub-select. However, you are not.

The subquery is just returning a value -- not a value with a name. So, the AS MENTIONED_USER in your version does nothing. This is a bit tricky, I guess. One way to think of the scalar subquery is that it is just another expression or function call. Things that happen inside it don't affect the outer query -- except for the value being returned.

The correct syntax is to put the column alias outside the subselect, not inside it:

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;