Add column with brains to view 1

[madanthrax]

Hi,

I have access to a read only view from the printing dept DB in our organisation. We all use MS SQL 2005. The DB is poorly designed and is out of my control.

Through a series of views of views I piece the data together and use it on our website as we want to display it.

The pages display book details. There are two series of books, guides and requirements. The series information is is next to the title (SeriesInfo in the DB). There is an old and a new form of the series pattern, for example: GS-R-2, GS-G-3, and SSR4, SSG2. The R or G at character 4 in the first pair and the R or G at character 3 in the second pair denote Requirement or Guide.

I want to create a view that parses the 'SeriesInfo' column string to look for these characters in these positions and depending on the result put a 1 or 2 value dynamically in an extra column called Category.

I have no real idea what I am doing in SQL code but I have put this together from info gathered from the web:

USE [Documents]
GO
/****** Object:  View [dbo].[vw_WebDocs5]    Script Date: 09/23/2010 17:31:07 ******/
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vw_WebDocs5]
     (PubId, Title, Language, ElectronicFile, FileSize, SeriesInfo, Released,  
CASE WHEN SUBSTRING(SeriesInfo, 4, 1) = 'R' THEN '1' AS Category
	       WHEN SUBSTRING(SeriesInfo, 3, 1) = 'R' THEN '1' AS Category
	       ELSE '2' AS Category
END )
FROM         dbo.vw_WebDocs4

Naturally I can't get this to work. I am not even sure if this is the right way to go about it.

Any help would be gratefully appreciated,

regards,

Anthony.

[sub]"Nothing is impossible until proven otherwise"[/sub]​

 

[gmmastros]

Not bad for a first attempt.

CREATE VIEW [dbo].[vw_WebDocs5]
As
Select PubId, Title, Language, ElectronicFile, FileSize, SeriesInfo, Released,  
       CASE WHEN SUBSTRING(SeriesInfo, 4, 1) = 'R' THEN '1'
            WHEN SUBSTRING(SeriesInfo, 3, 1) = 'R' THEN '1'
            ELSE '2'
            END As Category
FROM   dbo.vw_WebDocs4

Notice a couple things. First, you were missing the AS keyword after the declaration and before the query. You were also missing the select keyword. Most importantly, you were trying to put the column alias (as Category) on each branch of the case/when. You need to put it after the end.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[madanthrax]

George,

Thanks a lot for the swift help.

Its so obvious when its shown to you, I feel stupid.

Several hours ago I think there was a Select in there...

regards,

Anthony.

[sub]"Nothing is impossible until proven otherwise"[/sub]​

 

[SQLSister]

I want to caution you about using views as sources for other views. These can end up being extremely slow becasue they cannot be indexed past the first view and have to generate the entire view each time. We had some ill-advised developers who used this techinique throughout one of our datbases and almost lost a multi-million dollar client due to the resulting performance problems. Views calling other views are a bad idea in general. Try to avoid doing this.

"NOTHING is more important in a database than integrity." ESquared