Author |
Topic |
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-24 : 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.vSalesPersonGOSELECT TOP 3 SalesPersonID, FirstName, LastName, CAST(SalesYTD AS dec(12,2)) 'YTD Sales'FROM Sales.SalesPersonGoError msg:Msg 207, Level 16, State 1, Line 1Invalid column name 'FirstName'.Msg 207, Level 16, State 1, Line 1Invalid 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 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-24 : 14:34:07
|
Maybe the view put some ALIAS on the column names from the table?TrySELECT TOP 3 SalesPersonID, FName, LName, CAST(SalesYTD AS dec(12,2)) 'YTD Sales'FROM Sales.SalesPersonThe only sure way to check this is to look at the view's definition.Peter LarssonHelsingborg, Sweden |
 |
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-24 : 14:47:34
|
Peter, I get the same error message changed to:Error msg:Msg 207, Level 16, State 1, Line 1Invalid column name 'FName'.Msg 207, Level 16, State 1, Line 1Invalid 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
30421 Posts |
Posted - 2006-11-24 : 14:52:06
|
The view Sales.vSalesPerson most probably do like this when presenting the dataSELECT spid AS SalesPersonID, fn as FirstName, ln AS LastName, CAST(SalesYTD AS dec(12,2)) 'YTD Sales'FROM Sales.SalesPersonWhat 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 LarssonHelsingborg, Sweden |
 |
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-24 : 14:57:06
|
Run sp_help Sales.SalesPerson or even justselect top 1 * from Sales.SalesPersonto find out what the column nmaes are in the table. You're just using the wrong names. |
 |
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-24 : 15:20:02
|
Running: select top 1 * from Sales.SalesPersonI 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.000There's no name values returnedHowever when I run the original veiw I get:SalesPID FirstName LastName YTD Sales268 Stephen Jiang 677558.47275 Michael Blythe 4557045.05276 Linda Mitchell 5200475.23And 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
30421 Posts |
Posted - 2006-11-24 : 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 LarssonHelsingborg, Sweden |
 |
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-24 : 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
89 Posts |
Posted - 2006-11-24 : 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 errorsIt 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
30421 Posts |
Posted - 2006-11-24 : 15:32:26
|
Try thisselect text from syscomments where text like '%sales.vsalesperson%'Peter LarssonHelsingborg, Sweden |
 |
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-24 : 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
30421 Posts |
Posted - 2006-11-24 : 15:38:40
|
Try without schema, like thisselect text from syscomments where text like '%vsalesperson%'Peter LarssonHelsingborg, Sweden |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-24 : 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 likeselect firstname, lastname from [Person].[Contact]Peter LarssonHelsingborg, Sweden |
 |
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-24 : 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 muchIt is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
 |
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-24 : 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
1064 Posts |
Posted - 2006-11-24 : 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
22859 Posts |
Posted - 2006-11-25 : 03:26:43
|
"select text from syscomments where text like '%sales.vsalesperson%'"I would have gone forsp_helptext 'Sales.vSalesPerson'insteadKristen |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-25 : 03:43:26
|
ACtually I only typed"vSalesPerson"Peter LarssonHelsingborg, Sweden |
 |
|
Kristen
Test
22859 Posts |
Posted - 2006-11-25 : 05:59:04
|
Yeah, you suggested both, I was just meaning that sp_helptext gives a more user-friendly output IMOKristen |
 |
|
|