SQL ORA-00998: 必须用列别名命名这个表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42326426/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00998: must name this expression with a column alias
提问by melissa
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_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_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?
我仍然收到上述错误，我做错了什么？
回答by miracle173
Lookup the Oracle Error Message Manual of the current Oracle version. Here the error is mentioned but without additional information.
查找当前 Oracle 版本的Oracle 错误消息手册。这里提到了错误，但没有附加信息。
In such a case look up the Oracle Error Message Manual of version 9iFor 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.
在这种情况下，请查看9i 版的 Oracle 错误消息手册出于某些原因，我不知道很多错误消息在 9i 手册中有说明，但在更高版本的手册中没有。9i 是一个相当旧的版本，所以描述可能已经过时了。但它可能包含有价值的提示。
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.
ORA-00998 必须用列别名命名这个表达式
原因：在 CREATE VIEW 语句中使用了一个表达式或函数，但没有指定相应的列名。在视图中使用表达式或函数时，必须在 CREATE VIEW 语句中显式指定视图的所有列名。
操作：在视图名称后的括号中为视图中的每一列输入一个列名称。
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.
内部选择列表中的列别名没有用，可以删除。
回答by Gordon Linoff
The problem with your query is that each column in the create tableneeds to have a name. You think you are assigning a name in the sub-select. However, you are not.
您的查询的问题是中的每一列都create table需要有一个名称。您认为您正在子选择中分配名称。然而，你不是。
The subquery is just returning a value -- not a value with a name. So, the AS MENTIONED_USERin 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.
子查询只是返回一个值——而不是带有名称的值。所以，AS MENTIONED_USER在你的版本中什么都不做。我想这有点棘手。考虑标量子查询的一种方式是它只是另一个表达式或函数调用。它内部发生的事情不会影响外部查询——除了返回的值。
The correct syntax is to put the column alias outsidethe subselect, not insideit:
正确的语法是将列别名放在子选择之外，而不是里面：
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;
