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
Solved

CREATE SQL VIEW

Posted on 2010-09-12
13
541 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 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