为 oracle 创建视图时出现语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19643925/
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
Syntax error while creating view for oracle
提问by user2929533
My query:
我的查询：
CREATE VIEW SOME_VIEW2 AS
(
SELECT to_char(tbl_albums.album), COUNT(tbl_songs.id) AS "Songs in album"
FROM tbl_songs
LEFT JOIN tbl_albums
ON tbl_songs.album_id = tbl_albums.id
LEFT JOIN tbl_bands
ON tbl_albums.band_id = tbl_bands.id
WHERE to_char(LOWER(TRIM(tbl_bands.band))) = 'metallica'
GROUP BY to_char(tbl_albums.album)
);
Error I got:
我得到的错误：
Error at Command Line:10 Column:12 Error report: SQL Error: ORA-00998: must name this expression with a column alias 00998. 00000 - "must name this expression with a column alias"
命令行中的错误：10 列：12 错误报告：SQL 错误：ORA-00998：必须使用列别名 00998 命名此表达式。00000 -“必须使用列别名命名此表达式”
This doesn't work, however I previously tried this:
这不起作用，但是我以前尝试过：
CREATE VIEW SOME_VIEW AS
(
SELECT * FROM tbl_albums
);
And this worked fine, want to ask, what is wrong with first query.
这工作得很好，想问一下，第一次查询有什么问题。
回答by JBC
All columns must be named in a view. Since you have a calculated column, i.e.
所有列都必须在视图中命名。由于您有一个计算列，即
to_char(tbl_albums.album)
You need to give it a name, like so:
你需要给它一个名字，像这样：
to_char(tbl_albums.album) "Album"
回答by Hamidreza
try this query :
试试这个查询：
CREATE VIEW SOME_VIEW2 (album , SongsInAlbum) AS
(
SELECT q.alb,q.counter FROM
(SELECT to_char(tbl_albums.album) as alb, rownum as counter
FROM tbl_songs
LEFT JOIN tbl_albums
ON tbl_songs.album_id = tbl_albums.id
LEFT JOIN tbl_bands
ON tbl_albums.band_id = tbl_bands.id
WHERE to_char(LOWER(TRIM(tbl_bands.band))) = 'metallica')q
);
