?
Solved

CREATE SQL VIEW

Posted on 2010-09-12
13
Medium Priority
?
547 Views
Last Modified: 2012-05-10
I am having a problem creating a VIEW on SQL. I have all the required tables built, however when I specify to use a DATABASE

USE ABC123
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'

I get the following error:

'CREATE VIEW' must be the first statement in a query batch.


What is causing this error?
0
Comment
Question by:TAMSCODAN
  • 6
  • 6
13 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33656550
The stupid SQL Server parser....

Put the word 'GO' on a line between to two commands.



Kent
0
 
LVL 11

Accepted Solution

by:
aelliso3 earned 668 total points
ID: 33656557
when you create a view, you are creating it in a specific database and it will use the database that you have it as the starting (or USE) db.
If you are planning on having it somehwere else, then just set the select statement as below with a fully qualified name:
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM ABC123.dbo.summary
WHERE role = 'noun'
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33656567
cool, however where would I put the GO statement.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1332 total points
ID: 33656569
USE ABC123
GO

CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33656574
wow, that was the issue! is there a way to delete the view in the script if it was already created? so i can run the script again.
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1332 total points
ID: 33656578
I'm working from memory, but I think that this is it:

  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'V'))
   
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33656582
U ARE DA MAN!!!! THANKS!!!!
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33657906
I added the GO Statement as you mentioned previously, and I added a DROP VIEW statement at the begining of the code. Looks like the query executes without errors, however I dont see any results. Any suggestions?
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33658059
Post the script that you're running.
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33658070
USE ABC123
GO
DROP VIEW abc_only
GO
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33658099

SELECT * FROM abc_only;

That should get you everything where role='noun'.

Are you saying that it returns no rows?  Check the role column and make sure that at least 1 row has the value 'noun'.  Remember that string comparison is case sensitive so it must be all lower case, too.


Kent
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33658147
There are 4 colums, letter, Font, inserted_date and role. The view that I want are letter, Font, inserted_date that contain noun in the role column. In reference to my database it has 4 entries. Are you saying to replace SELECT letter, Font, inserted_date with SELECT * FROM abc_only;
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33658587
Sure.  The view that you've created returns the rows that you want.   A view name can be queried almost every place that a table name is appropriate.


Kent
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question