Create a View NEED HELP!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create a View NEED HELP!!

Author

Topic

isjord
Starting Member

38 Posts

Posted - 2006-01-26 : 18:47:48

Create a view showing every order that was shipped to Spain. Name the destination column "DestinationSpain". Include code that checks if the view already exists. If it does, it should be dropped and re-created.

Use Northwind
Go
DROP VIEW Spain_Orders
Go
CREATE VIEW Spain_Orders
AS
SELECT *
FROM Orders
WHERE ShipCountry = 'Spain'

this is what I get.

server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'Spain_Orders', because it does not exist in the system catalog.

albanie

Kristen
Test

22859 Posts

Posted - 2006-01-26 : 19:02:58

Until you have created the view it won't be there to be deleted. e.g. the first time you run this script.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-26 : 19:35:06

Good luck with your homework assignment!

isjord
Starting Member

38 Posts

Posted - 2006-01-26 : 19:50:03

USE Northwind
Go
DROP View Spain_Orders
Go
CREATE VIEW Spain_orders
AS
Select
Orders Order ID AS Order_ID

FROM Orders
WHERE ShipCountry = 'Spain'

is this what you mean?

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-26 : 19:50:50

check for existance of a view first before you drop it.

IF EXISTS (SELECT TABLE_NAME 
	   FROM   INFORMATION_SCHEMA.VIEWS 
	   WHERE  TABLE_NAME = N'view_name')
    DROP VIEW view_name
GO

CREATE VIEW view_name
AS 
	SELECT statements
GO

The above script is created using Query Analyser, File - New - Create View - Create View Basic Templates

----------------------------------
'KH'

isjord
Starting Member

38 Posts

Posted - 2006-01-26 : 20:37:50

IF EXISTS (SELECT Orders
FROM INFORMATION_SCHEMA.VIEWS
WHERE Orders = N'Spain_orders')
DROP VIEW Spain_orders
GO

CREATE VIEW OrderstoSpain
AS
SELECT
Orders Order_ID AS Order_ID
Orders CustomerID AS Customer_ID
Orders ShipCountry AS DestinationSpain
GO

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Orders'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Orders'.
Server: Msg 156, Level 15, State 1, Procedure OrderstoSpain, Line 5
Incorrect syntax near the keyword 'AS'.

I just do not understand.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-26 : 20:44:30

only change the view_name to you view name which is Spain_orders

----------------------------------
'KH'

isjord
Starting Member

38 Posts

Posted - 2006-01-26 : 20:52:27

IF EXISTS (SELECT Spain_orders
FROM INFORMATION_SCHEMA.VIEWS
WHERE Orders = N'Spain_orders')
DROP VIEW Spain_orders
GO

CREATE VIEW Spain_orders
AS
SELECT
Orders Order_ID AS Order_ID
Orders CustomerID AS Customer_ID
Orders ShipCountry AS DestinationSpain
GO

erver: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Spain_orders'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Orders'.
Server: Msg 156, Level 15, State 1, Procedure Spain_orders, Line 5
Incorrect syntax near the keyword 'AS'.

albanie

isjord
Starting Member

38 Posts

Posted - 2006-01-26 : 21:10:43

USE Northwind
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'Spain_Orders')
DROP VIEW Spain_Orders
GO
CREATE VIEW Spain_Orders
AS
SELECT
[Ship Country] as [DestinationSpain]
FROM Orders
WHERE [Ship Country] = 'Spain'
GO

Server: Msg 207, Level 16, State 3, Procedure Spain_Orders, Line 3
Invalid column name 'Ship Country'.
Server: Msg 207, Level 16, State 1, Procedure Spain_Orders, Line 3
Invalid column name 'Ship Country'.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-26 : 21:27:38

>> Invalid column name 'Ship Country'.
The column 'Ship Country' does not exists in the table Orders

The column name is actually 'ShipCountry'

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 00:44:17

Learn SQL
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

isjord
Starting Member

38 Posts

Posted - 2006-01-27 : 06:53:08

USE Northwind
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'Spain_Orders')
DROP VIEW Spain_Orders
GO
CREATE VIEW Spain_Orders
AS
SELECT
[OrderID]
[CustomerID]
[OrderDate]
[ShippedDate]
[ShipCountry] AS [DestinationSpain]
FROM Orders
WHERE [ShipCountry] = 'Spain'
GO

Server: Msg 170, Level 15, State 1, Procedure Spain_Orders, Line 6
Line 6: Incorrect syntax near 'OrderDate'.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-27 : 08:02:29

separate the column name with comma.

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 08:05:27

albanie,I recommend you to read the links I have given

Madhivanan

Failing to plan is Planning to fail

isjord
Starting Member

38 Posts

Posted - 2006-01-27 : 18:25:25

USE Northwind
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'Spain_Orders')
DROP VIEW Spain_Orders
GO
CREATE VIEW Spain_Orders
AS
SELECT
[OrderID],
[CustomerID],
[OrderDate],
[ShippedDate],
[ShipCountry] AS [DestinationSpain],
FROM Orders
WHERE [ShipCountry] = 'Spain'
GO

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

albanie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-27 : 18:30:00

Remove the comma before the FROM.

Tara Kizer
aka tduggan

isjord
Starting Member

38 Posts

Posted - 2006-01-27 : 18:42:45

Thank you so much. I was just about to tell you that I figured that out. Thank you for your patience, I am new and need to learn this very quick. Again Thanks. I know I will need you again, but I try to do as much as I can first.

albanie

isjord
Starting Member

38 Posts

Posted - 2006-01-27 : 21:45:08

Have the script display all the rows from the first view.

SELECT * FROM Spain_Orders ORDER BY Order_ID

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Spain_Orders'.

albanie

isjord
Starting Member

38 Posts

Posted - 2006-01-27 : 22:27:40

I need help with this also

Write a stored procedure named sp_insert_orderDetails that allows you to add a discount to an order. The stored procedure should accept five input parameters: OrderID, ProductID, UnitPrice, Quantity, and DiscountValue.

using Northwind.

albanie

Kristen
Test

22859 Posts

Posted - 2006-01-28 : 01:52:45

"I need help with this also"

doesn't seem to be very compatible with your earlier comment:

"I know I will need you again, but I try to do as much as I can first"

Can't see you've done much ... errmmmm ... anything!

Sorry mate, but you have to learn how to do stuff, not just get people to do it for you.

Sorry again, but I'm outta here ...

Kristen

isjord
Starting Member

38 Posts

Posted - 2006-01-28 : 07:37:19

-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_insert_orderDetails'
AND type = 'P')
DROP PROCEDURE sp_insert_orderDetails
GO

CREATE PROCEDURE sp_insert_orderDetails
@Discount real
AS INSERT INTO
{[OrderID],
[ProductID],
[UnitPrice],
[Quantity],
[Discount]}
GO

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

I did what I could but just did not post it, you got me wrong. It did not work that's why I did not post.

Never judge a book by its cover!!

albanie

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources