Temp Column in VIEW - 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

General SQL Server Forums

New to SQL Server Programming

Temp Column in VIEW

Author

Topic

syntax_error_1
Starting Member

4 Posts

Posted - 2005-09-27 : 12:32:19

Not sure if I am approaching this correctly. I can get name, address, zip code from a table via a VIEW. But the problem is I need to create another column called "status" in the same view that is based on "customer_end_date" if end_date> GETDATE() then 'P'(pick-up) and end_date< GETDATE () then 'I'(install). Using a stored procedure this is easy...but how would I do this in a VIEW?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-27 : 13:27:09

Hi there, welcome to the forums. Check out CASE in Books Online for more detailed info, but the following should get you going:

create table yourTable ([name] varchar(10), address varchar(25), zipcode varchar(5), customer_end_date datetime)
insert into yourTable
	select 'Nathan', '123 Main Street', '92109', getdate() + 10 union
	select 'Natalie', '321 Main Street', '94583', getdate() -10
go


create view v_test
as
select 	[name],
	address,
	zipcode,
	case 
		when customer_end_date > getdate() then 'P'
		else 'I'
	end as 'status'
from yourTable
go

select * from v_test

drop view v_test
go
drop table yourTable
go

Nathan Skerl

syntax_error_1
Starting Member

4 Posts

Posted - 2005-09-27 : 17:46:40

Thanks for the response but I'm getting error that CASE is not supported in VIEWs. Works very well in Query Analyzer though

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-27 : 18:10:32

This is just a limitation of Enterprise Manager's Query Designer. Just create the code through Query Analyzer.

Nathan Skerl

syntax_error_1
Starting Member

4 Posts

Posted - 2005-09-27 : 23:44:17

Thanks nathans, it works in Query Analyzer. Now I have another issue.

How would I assign 'P' and 'I' to data meeting the criteria and leave all other data unchanged but not display it in my results?

case
when customer_end_date > getdate() then 'P'
when customer_start_date = getdate() then 'I'
else ''
end as 'status'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 01:46:54

case
when DateDiff(day,getdate(),customer_end_date) >0 then 'P'
when DateDiff(day,getdate(),customer_Start_date )=0 then 'I'
else ''
end as 'status'

Madhivanan

Failing to plan is Planning to fail

syntax_error_1
Starting Member

4 Posts

Posted - 2005-09-28 : 11:27:54

Thanks guys. All works well with accurate results. Now time to implement into MapPoint.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources