Need help calculating values in view code

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

General SQL Server Forums

New to SQL Server Programming

Need help calculating values in view code

Author

Topic

Bren582
Starting Member

14 Posts

Posted - 2008-01-26 : 15:30:49

I'm in the process of building various queries and I'm having issue calculation values in the following code. I need to calculate the maximum and minimum salary for exempt employees and include a column that calculates the difference in these numbers. I have no issue creating views and outputing data. When I try to calculate the values I receive errors:

My code is:

GO
IF OBJECT_ID ('Salaries') IS NOT NULL
DROP VIEW Salaries ;
GO
CREATE VIEW Salaries
AS
select max(Salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID where ExemptStatus='Exempt'
Group By Salary, ExemptStatus;
GO
SELECT * FROM [Salaries]

I'm receiving the following
Msg 4511, Level 16, State 1, Procedure Salaries, Line 3
Create View or Function failed because no column name was specified for column 1.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Salaries'.

Thanks folks..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-26 : 16:19:46

you need to assign a column name for the value: max(Salary)
maybe something like:
select max(salary) as [MaxSalary] from ...

Be One with the Optimizer
TG

Bren582
Starting Member

14 Posts

Posted - 2008-01-26 : 17:34:52

That did the trick TG.. Now I need to include a column that calculates the difference in these numbers.. I can run a seperate query that does this but how can I run it as part of the existing query?

Code to calculates the difference in these numbers:

SELECT Max(Salary), Min(Salary), Max(Salary) - Min(Salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID
Where ExemptStatus='Exempt'

Need top add to this query:

GO
IF OBJECT_ID ('Salaries') IS NOT NULL
DROP VIEW Salaries ;
GO
CREATE VIEW Salaries
AS
SELECT ExemptStatus, MAX(Salary) Max_Salary, MIN(Salary) Min_Salary from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID
Where ExemptStatus='Exempt'
Group By Salary, ExemptStatus;
GO
SELECT * FROM [Salaries]

Thanks..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-26 : 17:49:43

I thought I already answered this question