| Author |
 Topic  |
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
 Posted - 11/24/2006 : 14:32:33
|
Good day to everyone.
I ran these two queries the first one is a view and the second one is it's non-veiw counterpart.
How come the first one gives results and the second gives error?
(error pasted below)
SELECT TOP 3 SalesPersonID, FirstName, LastName,
CAST(SalesYTD AS dec(12,2)) 'YTD Sales'
FROM Sales.vSalesPerson
GO
SELECT TOP 3 SalesPersonID, FirstName, LastName,
CAST(SalesYTD AS dec(12,2)) 'YTD Sales'
FROM Sales.SalesPerson
Go
Error msg:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'FirstName'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LastName'.
(3 row(s) affected)
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
Edited by - nomadsoul on 11/24/2006 14:33:12
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/24/2006 : 14:34:07
|
Maybe the view put some ALIAS on the column names from the table?
Try
SELECT TOP 3 SalesPersonID, FName, LName,
CAST(SalesYTD AS dec(12,2)) 'YTD Sales'
FROM Sales.SalesPerson
The only sure way to check this is to look at the view's definition.
Peter Larsson
Helsingborg, Sweden |
 |
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/24/2006 : 14:47:34
|
Peter, I get the same error message changed to:
Error msg:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'FName'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LName'.
Im just wondering if there's something about a view that shuts down or overwrites it's normal counter part.
I'm just practicing my SQL today so it's not imperative that I find out but it certainly has me fuddled.
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/24/2006 : 14:52:06
|
The view Sales.vSalesPerson most probably do like this when presenting the data
SELECT spid AS SalesPersonID, fn as FirstName, ln AS LastName,
CAST(SalesYTD AS dec(12,2)) 'YTD Sales'
FROM Sales.SalesPerson
What I emphasise is that the column name from the VIEW is not necessarily the same column name on the table that the view is based on.
Peter Larsson
Helsingborg, Sweden |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 11/24/2006 : 14:57:06
|
Run
sp_help Sales.SalesPerson
or even just
select top 1 * from Sales.SalesPerson
to find out what the column nmaes are in the table. You're just using the wrong names. |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/24/2006 : 15:20:02
|
Running: select top 1 * from Sales.SalesPerson
I get:
SalesPersonID TerritoryID SalesQuota Bonus CommissionPct SalesYTD
268 NULL NULL 0.00 0.00 677558.4653
SalesLastYear rowguidID ModifiedDate
0.00 48754992-9EE0-4C0E-8C94-9451604E3E02 2001-01-28 00:00:00.000
There's no name values returned
However when I run the original veiw I get:
SalesPID FirstName LastName YTD Sales
268 Stephen Jiang 677558.47
275 Michael Blythe 4557045.05
276 Linda Mitchell 5200475.23
And there are no LastName or FirstName columns in the Sales.SalesPerson table.
Where could these names be possibly comeing from? I could look through the other tables but this will take awhile.
-
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/24/2006 : 15:24:33
|
Then the VIEW Sales.vSalesPerson most probably do a JOIN to another table, using SalesPersonID as a key.
Have you tried to look at the definition of the view Sales.vSalesPerson yet as I suggested?
Peter Larsson
Helsingborg, Sweden |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/24/2006 : 15:29:08
|
I'm wondering if it could be a schema thing?
Anyway thanks for the great replies.
I've got to go for now but I will return later. I don't know if this forum allows image uploads becuase I would show you a screenshot of the DB, tables and query.
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/24/2006 : 15:31:38
|
Have you tried to look at the definition of the view Sales.vSalesPerson yet as I suggested?
yes sir I ran the query and still getting invalid colum name errors
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/24/2006 : 15:32:26
|
Try this
select text from syscomments where text like '%sales.vsalesperson%'
Peter Larsson
Helsingborg, Sweden |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/24/2006 : 15:37:49
|
Peter, I ran:select text from syscomments where text like '%sales.vsalesperson%'
and it returned a field called "text" with: (0 row(s) affected)
The database examples are from a book called: Beginning SQL Server 2005 Express by Rick Dobson.
by Apress and I'm practicing examples from it.
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/24/2006 : 15:38:40
|
Try without schema, like this
select text from syscomments where text like '%vsalesperson%'
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/24/2006 : 15:41:55
|
Google is our friend. This is the definition for the view Sales.vSalesPersonCREATE VIEW [Sales].[vSalesPerson]
AS
SELECT
s.[SalesPersonID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,[JobTitle] = e.[Title]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,[StateProvinceName] = sp.[Name]
,a.[PostalCode]
,[CountryRegionName] = cr.[Name]
,[TerritoryName] = st.[Name]
,[TerritoryGroup] = st.[Group]
,s.[SalesQuota]
,s.[SalesYTD]
,s.[SalesLastYear]
FROM [Sales].[SalesPerson] s
INNER JOIN [HumanResources].[Employee] e
ON e.[EmployeeID] = s.[SalesPersonID]
LEFT OUTER JOIN [Sales].[SalesTerritory] st
ON st.[TerritoryID] = s.[TerritoryID]
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]So you would need to write something like
select firstname, lastname from [Person].[Contact]
Peter Larsson
Helsingborg, Sweden |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/24/2006 : 17:25:43
|
Peso, thanks, I found the view in the DB, I didn't know it would already be there . I thought I was writing a fresh view based upon the Sales.SalesPerson table that was already there.
Thanks for the help from you guys. I hope I didn't inconvienience you too much
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/24/2006 : 17:50:32
|
Peso, could you tell me exactly what you typed in google to get that result?
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 11/24/2006 : 19:19:26
|
Nice detective work.
I'll bet it was something like:
"Sales.vSalesPerson" "create view"
Go for "I'm feeling lucky" and you will find the SQL.
BTW - you can see the view definition by double clicking the view in Enterprise Manager.
|
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 11/25/2006 : 03:26:43
|
"select text from syscomments where text like '%sales.vsalesperson%'"
I would have gone for
sp_helptext 'Sales.vSalesPerson'
instead
Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/25/2006 : 03:43:26
|
ACtually I only typed
"vSalesPerson"
Peter Larsson
Helsingborg, Sweden |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 11/25/2006 : 05:59:04
|
Yeah, you suggested both, I was just meaning that sp_helptext gives a more user-friendly output IMO
Kristen |
|
|
| |
Topic |
|
A view question