SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 A view question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/24/2006 :  14:32:33  Show Profile  Reply with Quote
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
30240 Posts

Posted - 11/24/2006 :  14:34:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/24/2006 :  14:47:34  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 11/24/2006 :  14:52:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/24/2006 :  14:57:06  Show Profile  Reply with Quote
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.
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/24/2006 :  15:20:02  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 11/24/2006 :  15:24:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/24/2006 :  15:29:08  Show Profile  Reply with Quote
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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/24/2006 :  15:31:38  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 11/24/2006 :  15:32:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try this
select text from syscomments where text like '%sales.vsalesperson%'


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/24/2006 :  15:37:49  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 11/24/2006 :  15:38:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try without schema, like this

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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 11/24/2006 :  15:41:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/24/2006 :  17:25:43  Show Profile  Reply with Quote
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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/24/2006 :  17:50:32  Show Profile  Reply with Quote
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
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 11/24/2006 :  19:19:26  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/25/2006 :  03:26:43  Show Profile  Reply with Quote
"select text from syscomments where text like '%sales.vsalesperson%'"

I would have gone for

sp_helptext 'Sales.vSalesPerson'

instead

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 11/25/2006 :  03:43:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ACtually I only typed

"vSalesPerson"


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/25/2006 :  05:59:04  Show Profile  Reply with Quote
Yeah, you suggested both, I was just meaning that sp_helptext gives a more user-friendly output IMO

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000