March 13, 2005 at 7:31 pm
I am new to SQL and I am not sure if this is a common thing or not but hopefully someone here can answer it for me. Created a script which does work I can see the tables but it says that the batch query was successful with errors. Here is a copy of the script:
USE Northwind
GO
IF EXISTS (SELECT Orders FROM INFORMATION_SCHEMA.VIEWS
WHERE Orders = 'DestinationSpain')
DROP VIEW DestinationSpain
GO
CREATE VIEW DestinationSpain
AS
SELECT OrderID, ShipCountry AS DestinationSpain
FROM Orders
WHERE ShipCountry = 'Spain'
WITH CHECK OPTION
GO
SELECT *FROM DestinationSpain
GO
IF EXISTS (SELECT Orders FROM INFORMATION_SCHEMA.VIEWS
WHERE Orders = 'NumOrders')
DROP VIEW NumOrders
GO
CREATE VIEW NumOrders
AS
SELECT ShipCountry, COUNT(*) AS [NumOrders]
FROM Orders
GROUP BY ShipCountry
GO
SELECT *FROM DestinationSpain
GO
SELECT *FROM NumOrders
GO
CREATE VIEW TotalOrdersToSpain
AS
SELECT COUNT(*) AS [TotalOrdersToSpain]
FROM Orders
WHERE ShipCountry = 'Spain'
GROUP BY ShipCountry
GO
SELECT *FROM TotalOrdersToSpain
GO
Is this common when you do a drop view or did I do something wrong??
Thanks....
March 14, 2005 at 1:41 am
Your SQL CREATE VIEW TotalOrdersToSpain is not preceded by existence check and DROP. Maybe this view already existed in the database?
Did you run the script in Query Analyzer? If yes, then you should receive the "There is already an object named.." error message - but if it was launched through some other tool, maybe you just get the general message if there are several statements in the batch.
March 14, 2005 at 7:07 am
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'DestinationSpain')
DROP VIEW DestinationSpain
GO
CREATE VIEW DestinationSpain
AS
SELECT OrderID, ShipCountry AS DestinationSpain
FROM Orders
WHERE ShipCountry = 'Spain'
WITH CHECK OPTION
GO
SELECT *FROM DestinationSpain
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'NumOrders')
DROP VIEW NumOrders
GO
CREATE VIEW NumOrders
AS
SELECT ShipCountry, COUNT(*) AS [NumOrders]
FROM Orders
GROUP BY ShipCountry
GO
SELECT *FROM DestinationSpain
GO
SELECT *FROM NumOrders
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'TotalOrdersToSpain')
DROP VIEW [TotalOrdersToSpain]
GO
CREATE VIEW TotalOrdersToSpain
AS
SELECT COUNT(*) AS [TotalOrdersToSpain]
FROM Orders
WHERE ShipCountry = 'Spain'
GROUP BY ShipCountry
GO
SELECT *FROM TotalOrdersToSpain
GO
Vasc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply