A view question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

A view question

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.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

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?

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

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 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

30421 Posts

Posted - 2006-11-24 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-24 : 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

89 Posts

Posted - 2006-11-24 : 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

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 Larsson
Helsingborg, 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 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

30421 Posts

Posted - 2006-11-24 : 15:32:26

Try this
select text from syscomments where text like '%sales.vsalesperson%'

Peter Larsson
Helsingborg, 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 this

select text from syscomments where text like '%vsalesperson%'

Peter Larsson
Helsingborg, 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.vSalesPerson

CREATE 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

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 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

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 for

sp_helptext 'Sales.vSalesPerson'

instead

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-25 : 03:43:26

ACtually I only typed

"vSalesPerson"

Peter Larsson
Helsingborg, 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 IMO

Kristen