First Foray into VIEWS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

First Foray into VIEWS

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-17 : 15:55:33

Ok, having just been tasked with creating a new view for a table (actually, modifying an existing one), and admitting that i have never worked with "views" before, i'm anxious to understand this. I have a basic understanding, if not a complete one, regarding what a view is in SQL. My closest frame of reference is my years of experience working with Access Data Projects with SQL backends. Frequently i would create forms with subforms and parent-child relationships. Those subforms in Access, to me, seem to resemble what we're now trying to do with Views in SQL. Nontheless, what i've been tasked with is taking an existing view, and adding additional columns from the same table to it. So, below is the current view:
/****** Object: View [ArrestDetailed] Script Date: 11/17/2011 2:46:21 PM ******/
CREATE VIEW [ArrestDetailed]
AS
SELECT A.ArrestNo, A.OffenseNo, A.ArrestType,
CASE
WHEN A.ArrestType = 'A' THEN 'Arrest'
WHEN A.ArrestType = 'C' THEN 'Commitment'
WHEN A.ArrestType = 'D' THEN 'DVI'
WHEN A.ArrestType = 'N' THEN 'Notice to Appear'
WHEN A.ArrestType = 'S' THEN 'Criminal Summons'
WHEN A.ArrestType = 'W' THEN 'Warrant/OTTIC'
WHEN A.ArrestType = 'T' THEN 'Writ'
End As "ArrestDescript",
A.WarStatus,
CASE
WHEN A.ArrestType = 'N' AND A.WarStatus = 'A' THEN 'Active'
WHEN A.ArrestType = 'A' AND A.WarStatus = 'A' THEN 'Active'
WHEN A.ArrestType = 'A' and A.WarStatus = 'A' THEN 'Active'
WHEN A.ArrestType = 'C' and A.WarStatus = '1' THEN 'Active'
WHEN A.ArrestType = 'C' and A.WarStatus = '2' THEN 'Served'
WHEN A.ArrestType = 'C' and A.WarStatus = '4' THEN 'Served'
WHEN A.ArrestType = 'C' and A.WarStatus = '9' THEN 'Recalled'
WHEN A.ArrestType = 'C' and A.WarStatus = 'A' THEN 'Recalled'
WHEN A.ArrestType = 'C' and A.WarStatus = 'H' THEN 'On Hold'
WHEN A.ArrestType = 'W' and A.WarStatus = 'H' THEN 'On Hold'
WHEN A.ArrestType = 'W' and A.WarStatus = 'X' THEN 'Rejected by Judge'
WHEN A.ArrestType = 'W' and A.WarStatus = 'J' THEN 'Sent to Judge'
WHEN A.ArrestType = 'W' and A.WarStatus = 'A' THEN 'Affidavit'
WHEN A.ArrestType = 'D' and A.WarStatus = '8' THEN 'Recalled'
WHEN A.ArrestType = 'D' and A.WarStatus = 'E' THEN 'Expired'
WHEN A.ArrestType = 'D' and A.WarStatus = 'N' THEN 'Temp Not Served'
WHEN A.ArrestType = 'D' and A.WarStatus = 'D' THEN 'Temp Served'
WHEN A.ArrestType = 'D' and A.WarStatus = '8' THEN 'Recalled'
WHEN A.ArrestType = 'D' and A.WarStatus = 'P' THEN 'Perm Served'
WHEN A.ArrestType = 'D' and A.WarStatus = '7' THEN 'Perm Not Served'
WHEN A.ArrestType = 'N' and A.WarStatus = 'A' THEN 'Active'
WHEN A.ArrestType = 'S' and A.WarStatus = 'B' THEN 'Recalled'
WHEN A.ArrestType = 'S' and A.WarStatus = '5' THEN 'Active'
WHEN A.ArrestType = 'S' and A.WarStatus = '6' THEN 'Served'
WHEN A.ArrestType = 'T' and A.WarStatus = '3' THEN 'Active'
WHEN A.ArrestType = 'T' and A.WarStatus = '4' THEN 'Served'
WHEN A.ArrestType = 'T' and A.WarStatus = '0' THEN 'Recalled'
WHEN A.ArrestType = 'W' and A.WarStatus = 'R' THEN 'Recalled'
WHEN A.ArrestType = 'W' and A.WarStatus = 'S' THEN 'Served'
WHEN A.ArrestType = 'W' and A.WarStatus = 'W' THEN 'Active'
End As "WarDescript",
A.Ecsoid, A.LName, A.FName, A.MName, A.DOB, A.Race, A.Sex, A.Arr_Date, A.ReportDate,
sAgency =
CASE WHEN len(Arrestno) = 14 THEN SUBSTRING(Arrestno, 1, 3) WHEN len(Arrestno) = 15 THEN SUBSTRING(Arrestno, 1, 4)
END
FROM Arrest.dbo.Arrest A WITH (READUNCOMMITTED)
LEFT JOIN Arrest.dbo.CfgArrType T WITH (READUNCOMMITTED) ON A.ArrestType = T.ArrestType AND T.GroupID = 1
LEFT JOIN Arrest.dbo.CfgArrType W WITH (READUNCOMMITTED) ON A.WarStatus = W.WarStatus AND W.GroupID = 2
GO

What i understand i must do at this point, is to add the fields that the customer wants in this new view. As i understant it, that would be done by modifying this line:

SELECT A.ArrestNo, A.OffenseNo, A.ArrestType,

to now look like this:

SELECT A.ArrestNo, A.OffenseNo, A.ArrestType,A.OC_HOUSENU, A.OC_QUAD, A.OC_STREET, A.OC_HOUSESU, A.OC_CITY, A.OC_STATE, A.OC_ZIP, A.OC_ZONE, A.OC_DISTGRD

Those are the additional fields we want in the new view.

I'm also aware that, in this create view script that i generated, i must change the name for the new script, which would be:

CREATE VIEW [ArrestDetailedWithLocation]

However, i'm at a complete loss as to what my next step is in creating this new view. Any guidance on this would be greatly appreciated.

thanks

james

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-17 : 16:04:32

CREATE VIEW [ArrestDetailedWithLocation]
AS
SELECT A.ArrestNo, A.OffenseNo, A.ArrestType,A.OC_HOUSENU, A.OC_QUAD, A.OC_STREET, A.OC_HOUSESU, A.OC_CITY, A.OC_STATE, A.OC_ZIP, A.OC_ZONE, A.OC_DISTGRD,
CASE
WHEN A.ArrestType = 'A' THEN 'Arrest'
WHEN A.ArrestType = 'C' THEN 'Commitment'
WHEN A.ArrestType = 'D' THEN 'DVI'
WHEN A.ArrestType = 'N' THEN 'Notice to Appear'
WHEN A.ArrestType = 'S' THEN 'Criminal Summons'
WHEN A.ArrestType = 'W' THEN 'Warrant/OTTIC'
WHEN A.ArrestType = 'T' THEN 'Writ'
End As "ArrestDescript",
A.WarStatus,
CASE
WHEN A.ArrestType = 'N' AND A.WarStatus = 'A' THEN 'Active'
WHEN A.ArrestType = 'A' AND A.WarStatus = 'A' THEN 'Active'
WHEN A.ArrestType = 'A' and A.WarStatus = 'A' THEN 'Active'
WHEN A.ArrestType = 'C' and A.WarStatus = '1' THEN 'Active'
WHEN A.ArrestType = 'C' and A.WarStatus = '2' THEN 'Served'
WHEN A.ArrestType = 'C' and A.WarStatus = '4' THEN 'Served'
WHEN A.ArrestType = 'C' and A.WarStatus = '9' THEN 'Recalled'
WHEN A.ArrestType = 'C' and A.WarStatus = 'A' THEN 'Recalled'
WHEN A.ArrestType = 'C' and A.WarStatus = 'H' THEN 'On Hold'
WHEN A.ArrestType = 'W' and A.WarStatus = 'H' THEN 'On Hold'
WHEN A.ArrestType = 'W' and A.WarStatus = 'X' THEN 'Rejected by Judge'
WHEN A.ArrestType = 'W' and A.WarStatus = 'J' THEN 'Sent to Judge'
WHEN A.ArrestType = 'W' and A.WarStatus = 'A' THEN 'Affidavit'
WHEN A.ArrestType = 'D' and A.WarStatus = '8' THEN 'Recalled'
WHEN A.ArrestType = 'D' and A.WarStatus = 'E' THEN 'Expired'
WHEN A.ArrestType = 'D' and A.WarStatus = 'N' THEN 'Temp Not Served'
WHEN A.ArrestType = 'D' and A.WarStatus = 'D' THEN 'Temp Served'
WHEN A.ArrestType = 'D' and A.WarStatus = '8' THEN 'Recalled'
WHEN A.ArrestType = 'D' and A.WarStatus = 'P' THEN 'Perm Served'
WHEN A.ArrestType = 'D' and A.WarStatus = '7' THEN 'Perm Not Served'
WHEN A.ArrestType = 'N' and A.WarStatus = 'A' THEN 'Active'
WHEN A.ArrestType = 'S' and A.WarStatus = 'B' THEN 'Recalled'
WHEN A.ArrestType = 'S' and A.WarStatus = '5' THEN 'Active'
WHEN A.ArrestType = 'S' and A.WarStatus = '6' THEN 'Served'
WHEN A.ArrestType = 'T' and A.WarStatus = '3' THEN 'Active'
WHEN A.ArrestType = 'T' and A.WarStatus = '4' THEN 'Served'
WHEN A.ArrestType = 'T' and A.WarStatus = '0' THEN 'Recalled'
WHEN A.ArrestType = 'W' and A.WarStatus = 'R' THEN 'Recalled'
WHEN A.ArrestType = 'W' and A.WarStatus = 'S' THEN 'Served'
WHEN A.ArrestType = 'W' and A.WarStatus = 'W' THEN 'Active'
End As "WarDescript",
A.Ecsoid, A.LName, A.FName, A.MName, A.DOB, A.Race, A.Sex, A.Arr_Date, A.ReportDate,
sAgency =
CASE WHEN len(Arrestno) = 14 THEN SUBSTRING(Arrestno, 1, 3) WHEN len(Arrestno) = 15 THEN SUBSTRING(Arrestno, 1, 4)
END
FROM Arrest.dbo.Arrest A WITH (READUNCOMMITTED)
LEFT JOIN Arrest.dbo.CfgArrType T WITH (READUNCOMMITTED) ON A.ArrestType = T.ArrestType AND T.GroupID = 1
LEFT JOIN Arrest.dbo.CfgArrType W WITH (READUNCOMMITTED) ON A.WarStatus = W.WarStatus AND W.GroupID = 2
GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-17 : 16:06:53

That's it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-17 : 16:38:27

Yes. There's nothing special about regular views. It's really just a saved query. All you do is wrap it into a view.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 07:33:25

So is it accurate to assume that the view functions as a sort of temporary table, which can be accessed by the front end application to display the data that the view is pulling together? Also, at this point, can i create a new column within the view to concatenate some of the new data that i pulled into this view? This new column would not exist in any of the tables from which the view is pulling it's data, but would exist just in the view. Is that possible?

Eddie M
Starting Member

14 Posts

Posted - 2011-11-18 : 07:43:49

If I may jump in here ....

Yes, a view functions a bit like a temporary table, but with an important difference: It is re-executed every time you use it. So any query that references the view will always work on the most up-to-date data. By contast, if you store the results of a query in a temporary table, any subsequent query that uses the table might be working with out-of-date data.

And, yes, a view can contain a column that does not exist in the original tables. In fact, that's quite common. It's typically used to hold some derived value - perhaps the result of an expression ( as in your WarDescript column), or to bring in an expanded value from a lookup table.

Just think of a view as a SELECT statement that's permanently stored in your database.

Ed.

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 08:03:15

quote:
Originally posted by WJHamel

So is it accurate to assume that the view functions as a sort of temporary table, which can be accessed by the front end application to display the data that the view is pulling together?

Probably best not to think of it quite like that ...

I prefer to think of it as SQL substituting the "FROM MyView" statement in my query with all the code from the definition of the view.

The Optimiser will then remove columns and tables (defined in the view) which are not relevant to the current query. So having extra columns in the view should not adversely effect performance if they are not actually referenced in the final query.

But having said that, it is possible to place an index on the view, in which case the physical data is maintained (in the index) and will be updated as the data changes in the underlying table(s). That makes the queries against the view faster, because the index contains the actual data, but at the cost of having to update the view's index whenever the underlying data changes. That's generally worthwhile if there are considerably more READs than WRITEs.

Some other comments:

Personally I would prefer to see [ArrestDescript] created by JOINing [ArrestType] to a lookup-table. Otherwise all the individual descriptions have to be maintained in this view, and any other views, or other code, where they are needed. Alternatively put them in a VIEW of their own and LEFT OUTER JOIN that. Ditto [WarDescript]

"WITH (READUNCOMMITTED)" scares me a lot. I appreciate you didn't write the original, but the use of NOLOCK and READUNCOMMITTED hints usually suggests that the original author was using it to improve performance but did not understand the potential side effects and consequences.

Here's what the DOCs say: "This [READUNCOMMITTED] may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all)"

So if the user needs to rely on the data (rather than it just being some quick-and-dirty usage) then these should almost certainly not be in there.

If they were put in for performance reasons, in particular if there were READs blocking WRITEs, then consider setting the database to READ_COMMITTED_SNAPSHOT instead. This option didn't exist in SQL 2000, so if you application has been migrated back from SQL 2000 that may be why they are there.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 08:56:58

That "NOLOCK" statement gets used like wildfire around here. I've kept mum about it because those who are proponents of using it are far more experienced than I.

So how do i create this new column in the view? I know what and how i want to concatenate the data in this new column, i'm just unclear about how to "create" that column in this view.

thanks

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 10:24:56

So why would the following not work to create the cocatenation that i seek?:
SELECT (
'The location of this offense was' + A.OC_Housenu + ' ' + A.OC_Quad +
' ' + A.OC_STREET + ' ' + A.OC_HOUSESU + A.OC_CITY + ' ' + A.OC_STATE
+ ' ' + A.OC_ZIP + ' ' + A.OC_ZONE + ' ' + A.OC_DISTGRID
) AS FULLLOCATION
FROM arrest.dbo.arrest,
All of the fields listed in there are varchars

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 11:00:23

quote:
Originally posted by WJHamel

That "NOLOCK" statement gets used like wildfire around here. I've kept mum about it because those who are proponents of using it are far more experienced than I.

There's only a slim chance that they are right. e.g. if the tables with the hint are never updated ... but if that's the case they won't be any locking-contention, so it wouldn't be needed. Its also possible that they have programmed around the possible side effects, but I think that is extremely unlikely

However, if they are being used wrongly the risk to the business is huge - e.g. a business decision taken on a report which happens to omit some critical data.

"So how do i create this new column in the view?"


CREATE VIEW [ArrestDetailedWithLocation]
AS
SELECT ... the original columns ... ,
      'The location of this offense was' + A.OC_Housenu + ' ' 
          + A.OC_Quad + ' '
          + A.OC_STREET + ' '
          + A.OC_HOUSESU + A.OC_CITY + ' ' 
          + A.OC_STATE + ' ' 
          + A.OC_ZIP + ' ' 
          + A.OC_ZONE + ' ' 
          + A.OC_DISTGRID
      AS FULLLOCATION,
      ... any other columns you need ....
FROM ... original FROM / JOIN clause ...
     ... add any new JOINs that you need ...

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 11:06:04

quote:
Originally posted by WJHamel

So why would the following not work to create the cocatenation that i seek?:

Explain "not work" please.

If any one of those columns is NULL then the concatenation result will be NULL. If that's the problem then use the COALESCE() function:


    'The location of this offense was' 
        + COALESCE(A.OC_Housenu + ' ', '')
        + COALESCE(A.OC_Quad + ' ', '')
etc.

I think you need "offenCe" rather than "offenSe" ... well, over this side of the pond anyway!

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 11:09:22

The change you suggest is pretty close to what i was assuming i would do, but as soon as i set the script as an "ALTER VIEW" and then add that new select statement:
SELECT A.OC_HOUSENU, A.OC_QUAD, A.OC_STREET, A.OC_HOUSESU, A.OC_CITY, A.OC_STATE, A.OC_ZIP, A.OC_ZONE, A.OC_DISTGRD,
'The location of this offense was' + A.OC_Housenu + ' '
+ A.OC_Quad + ' '
+ A.OC_STREET + ' '
+ A.OC_HOUSESU + A.OC_CITY + ' '
+ A.OC_STATE + ' '
+ A.OC_ZIP + ' '
+ A.OC_ZONE + ' '
+ A.OC_DISTGRID
AS FULLLOCATION,
FROM ARREST.DBO.Arrest a

I get a syntax error on the AlterView component, stating "Incorrect Syntax: 'Alter View must the be only statment in the batch" as well as a syntax error on the FROM portion, just stating "Incorrect Syntax".

Any ideas why?

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 11:44:42

And i need to work on my pronounciation of HHHHHHHHERB because there's a !@#!#@!!! "H" IN IT!
I was wondering how the null values in some of those fields would affect the concat. putting the coalesce statements in there now.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 12:00:53

Flip friggin filth flarin filth! Ok, now that it's done and working, the project manager doesn't even want the concat in there at all! Just wants the new columns selected as more verbose intuitive values. Ack.

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 12:30:48

"Alter View must the be only statment in the batch"

Means that the ALTER VIEW command must be the only thing you execute. You cannot do


ALTER VIEW MyView
AS
SELECT Col1, Col2, ...
FROM MyTable

SELECT * FROM MyView

the SELECT statement, in red, is not permitted. Just do the Alter View first, then do the SELECT separately.

Or put "GO" on a line by itself after the ALTER VIEW statement - to separate it from the following SQL statement(s)

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-18 : 12:45:34

Got it. Thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources