|
|
Posted by Jim Devenish on 07/05/06 21:38
I have 3 views, two of which depend on the other:
CREATE VIEW dbo.CustomerListQueryAccounts
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 0)
CREATE VIEW dbo.CustomerListQueryProspects
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 1)
which depend on:
CREATE VIEW dbo.CustomerListQuery
AS
SELECT TOP 100 PERCENT
dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber,
dbo.NumberOfJobsPerCustomer.CountOfJobID,
dbo.NumberOfQuotesPerCustomer.CountOfQuoteID,
dbo.NumberOfComplaintsPerCustomer.CountOfComplaintID,
dbo.NumberOfNotesPerCustomer.CountOfCustomerNoteID,
dbo.NumberOfEnquiriesPerCustomer.CountOfEnquiryID
FROM dbo.Customers
LEFT OUTER JOIN
dbo.NumberOfJobsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfJobsPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfQuotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfQuotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfNotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfNotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfComplaintsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfComplaintsPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfEnquiriesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfEnquiriesPerCustomer.CustomerID
ORDER BY dbo.Customers.AccountName
These work well but I have an alternative version of this latter one
which has fewer columns. At present it is:
CREATE VIEW dbo.CustomerListQueryShorter
AS
SELECT dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber
FROM dbo.Customers
I now wish to make this one CustomerListQuery and so rename the
existing one CustomerListQueryOriginal (just to get is out of the way)
and change its first line, using the View's properties (in Enterprise
Manager) to :
CREATE VIEW dbo.CustomerListQueryOriginal
I then rename CustomerListQueryShorter to CustomerListQuery and change
its first line to
CREATE VIEW dbo.CustomerListQuery
Now when I 'Return all rows' of CustomerListQueryAccounts I get an
error message:
'dbo.CustomerListQueryAccounts' has more column names specified than
columns defined.
If however, I go into the design and then select Run, I get the correct
output reflecting the new version of CustomerListQuery. How do I get
the 'Return all rows' output to show the same.
It appears that the orginal version is still being used. How do I get
the system to replace this ?
Navigation:
[Reply to this message]
|