Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

CREATE SQL VIEW

Posted on 2010-09-12
13
Medium Priority
?
546 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

618 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