May 16, 2008 at 9:04 pm
Hello again.
I've got some code that is supposed to create a view:-- Mitch Curtis
-- A2view.sql
-- Set the active database to KWEA.
USE KWEA;
-- Drop view.
DROP VIEW Property_Owner_Table;
CREATE VIEW Property_Owner_Table
AS SELECT owner#, name
FROM Property_Owner;But it gives me this error:Msg 111, Level 15, State 1, Line 7
'CREATE VIEW' must be the first statement in a query batch.So I change the code to this (even though now I can't implicitly ensure I'm working on the right database):-- Mitch Curtis
-- A2view.sql
-- Drop view.
DROP VIEW Property_Owner_Table;
CREATE VIEW Property_Owner_Table
AS SELECT owner#, name
FROM Property_Owner;But it gives me the same error:Msg 111, Level 15, State 1, Line 7
'CREATE VIEW' must be the first statement in a query batch.So I change it again!-- Mitch Curtis
-- A2view.sql
CREATE VIEW Property_Owner_Table
AS SELECT owner#, name
FROM Property_Owner;It works now... the only problem is that now the code can't be executed multiple times which is a requirement of the problem.
Note that another requirement of the problem is that the code be contained within a single query file.
How can I do this? :crazy:
Cheers.
May 17, 2008 at 1:09 am
Why do you need to do this? It may make a difference...
--Jeff Moden
May 17, 2008 at 12:35 pm
Try this, it should work. The only thing i have changed is ive added the 'GO' after each statement.
-- Mitch Curtis
-- A2view.sql
-- Set the active database to KWEA.
USE KWEA;
GO
-- Drop view.
DROP VIEW Property_Owner_Table;
GO
CREATE VIEW Property_Owner_Table
AS SELECT owner#, name
FROM Property_Owner;
GO
This tells sql to execute upto the first GO statement, then move on to the next 'batch'.
Matt.
May 18, 2008 at 7:29 pm
Matt (5/17/2008)
Try this, it should work. The only thing i have changed is ive added the 'GO' after each statement.-- Mitch Curtis
-- A2view.sql
-- Set the active database to KWEA.
USE KWEA;
GO
-- Drop view.
DROP VIEW Property_Owner_Table;
GO
CREATE VIEW Property_Owner_Table
AS SELECT owner#, name
FROM Property_Owner;
GO
This tells sql to execute upto the first GO statement, then move on to the next 'batch'.
Matt.
Brilliant! Works like a treat! Thanks a lot!
Why do you need to do this? It may make a difference...
What do you mean? It's part of an assignment. Please don't say this is a "homework question" because I've clearly shown effort (code). :ermm:
Cheers!
May 19, 2008 at 4:49 am
Mybowlcut (5/18/2008)
Why do you need to do this? It may make a difference...
What do you mean? It's part of an assignment. Please don't say this is a "homework question" because I've clearly shown effort (code). :ermm:
Cheers!
Clearly, you have shown an effort and I certainly don't mind helping with homework when someone shows the type of effort you did in the original post.
No, the reason why I was asking was to determine if you needed a temporary structure like a table valued function or if a view would actually do the trick for you. Creating a view "inflight" is pretty unusual except when trying to create something like a partitioned view. Lot's of folks say "I need to do this" and something else is better... just wanted to make sure here. The other thing I wanted to make sure of was that you didn't need to do this to multiple DB's... if you did, we'd need to get a little "dynamic" SQL going.
The code that Matt offered is spot on for a script... because of the "Go's" in it, it's not possible for it to be a stored procedure and I wanted to make sure that's ok, too.
--Jeff Moden
May 19, 2008 at 5:24 am
Jeff Moden (5/19/2008)
Mybowlcut (5/18/2008)
Why do you need to do this? It may make a difference...
What do you mean? It's part of an assignment. Please don't say this is a "homework question" because I've clearly shown effort (code). :ermm:
Cheers!
Clearly, you have shown an effort and I certainly don't mind helping with homework when someone shows the type of effort you did in the original post.
No, the reason why I was asking was to determine if you needed a temporary structure like a table valued function or if a view would actually do the trick for you. Creating a view "inflight" is pretty unusual except when trying to create something like a partitioned view. Lot's of folks say "I need to do this" and something else is better... just wanted to make sure here. The other thing I wanted to make sure of was that you didn't need to do this to multiple DB's... if you did, we'd need to get a little "dynamic" SQL going.
The code that Matt offered is spot on for a script... because of the "Go's" in it, it's not possible for it to be a stored procedure and I wanted to make sure that's ok, too.
Ahhh ok. 🙂 To be honest, I'm just doing what the assignment says haha. I don't know what a partitioned view is or dynamic sql haha. At the moment I'm just trying to pass the unit, but when I finish uni I'll be using databases to support my game's data and then I'll take more of an interest. It said:
Based on your PropertyOwner and Property tables, create a view showing all
properties of each owner; display the content of the view; and then query the view to
display all owners who own two or less properties.
Oh no! I just realised I forgot to query it! Ahhh man gonna lose marks for that. 🙁 Oh well haha. 🙁
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply