Solved

CREATE SQL VIEW

Posted on 2010-09-12
13
543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 45

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 167 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 333 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 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 333 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 45

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 45

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 45

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
monitoring configuration for SQL server DB 32 60
PHP: Filling Out/Creating a PDF 29 157
SQL Server 2005 running VERY slowly on new hardware 22 55
SQL Procedure 7 45
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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