CODEWITHSUNDEEP
sql
pavuxun
pavuxun

Reputation: 431

Create view with computed column using a where clause

I want to add a computed column to a view, by performing a simple math operation on a column where the value of a corresponding column is a certain value. For example:

CREATE VIEW v_tracks 
AS 
SELECT
    (trackid + 1) as calcTrackId Where name like "WuTang",
    tracks.name,
    albums.Title AS album,
    media_types.Name AS media,
    genres.Name AS genres
FROM
    tracks
INNER JOIN albums ON Albums.AlbumId = tracks.AlbumId
INNER JOIN media_types ON media_types.MediaTypeId = tracks.MediaTypeId
INNER JOIN genres ON genres.GenreId = tracks.GenreId;

However, this doesn't work. I only want the track ID incremented by one when the name (referring to the name column in the view) is "WuTang". How can I do this?

Upvotes: 0

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Is this what you want?

(case when name = 'WuTang'
      then row_number() over (partition by name order by ?)
 end) as calcTrackId

The ? should correspond to the ordering that you care about. You can always use name if you don't care.

This will assign an incremental value to each WuTang track and NULL to others.

If you want the incremental value to be on all rows, not just the WuTang rows, then use a cumulative sum:

sum(case when name = 'WuTang' then 1 else 0 end) over (partition by name order by ?) as calcTrackId

Upvotes: 0

Brad
Brad

Reputation: 3591

Since you dont have SQL technology listed not sure, but this should work (or similar syntax depending on technology)

CREATE VIEW v_tracks 
AS 
SELECT
    CASE WHEN name LIKE 'WuTang' THEN trackid + 1 ELSE trackid END AS calcTrackId,
    tracks.name,
    albums.Title AS album,
    media_types.Name AS media,
    genres.Name AS genres
FROM
    tracks
INNER JOIN albums ON Albums.AlbumId = tracks.AlbumId
INNER JOIN media_types ON media_types.MediaTypeId = tracks.MediaTypeId
INNER JOIN genres ON genres.GenreId = tracks.GenreId;

Upvotes: 2

Related Questions