CODEWITHSUNDEEP
sqloracle-database
user2929533
user2929533

Reputation: 87

Syntax error while creating view for oracle

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.

Upvotes: 3

Views: 17357

Answers (2)

Hamidreza
Hamidreza

Reputation: 3128

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

Upvotes: 0

JBC
JBC

Reputation: 685

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"

Upvotes: 10

Related Questions