Syntax error while creating view for oracle

Question

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"

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.

Give an alias to the column to_char(tbl_albums.album) in your select list. — Taryn
– Taryn, Commented Oct 28, 2013 at 19:57
There is absolutely no need for the parentheses around the actual select statement. — user330315
– user330315, Commented Oct 29, 2013 at 9:13

JBC · Accepted Answer · 2013-10-28 20:11:03Z

10

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"

answered Oct 28, 2013 at 20:11

JBC

6851 gold badge9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hamidreza · Accepted Answer · 2013-10-29 08:28:16Z

0

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
);

edited Oct 29, 2013 at 8:28

answered Oct 29, 2013 at 7:42

Hamidreza

3,1581 gold badge20 silver badges15 bronze badges

Collectives™ on Stack Overflow

Syntax error while creating view for oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related