• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

CREATE SQL VIEW

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
TAMSCODAN
Asked:
TAMSCODAN
  • 6
  • 6
3 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
The stupid SQL Server parser....

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



Kent
0
 
aelliso3Commented:
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
 
TAMSCODANAuthor Commented:
cool, however where would I put the GO statement.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kent OlsenData Warehouse Architect / DBACommented:
USE ABC123
GO

CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
0
 
TAMSCODANAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
TAMSCODANAuthor Commented:
U ARE DA MAN!!!! THANKS!!!!
0
 
TAMSCODANAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
Post the script that you're running.
0
 
TAMSCODANAuthor Commented:
USE ABC123
GO
DROP VIEW abc_only
GO
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
TAMSCODANAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now