how to write a view with select statement

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

how to write a view with select statement

Author

Topic

ramu143
Yak Posting Veteran

64 Posts

Posted - 2008-09-25 : 01:03:09

drop table #mohan

select filedate, switchcode, legaport,sum(legatime)/60 as minutes,count(*) as noofcalls into #mohan from month09..xcda (nolock)
where legaport like 'dac%' and filedate like '2008-09-04' group by filedate, switchcode,legaport order by filedate, switchcode,legaport

ALTER TABLE #MOHAN ADD ACD float

update #MOHAN set ACD= minutes/noofcalls

select * from #MOHAN where ACD<0.5 and noofcalls>1 order by ACD ,noofcalls

with this select statements i need to write a view are procedure

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 01:12:31

[code]CREATE VIEW YourView
AS
select filedate,
switchcode,
legaport,
minutes,
noofcalls,
minutes*1.0/noofcalls as ACD
FROM
(
select filedate, switchcode, legaport,sum(legatime)*1.0/60 as minutes,count(*) as noofcalls into #mohan from month09..xcda (nolock)
where legaport like 'dac%' and filedate like '2008-09-04'
group by filedate, switchcode,legaport
)t
order by filedate, switchcode,legaport
[/code]
Also keep in mind that you wont have any use in keeping order by inside view. unless you order the records explicitly using order by while selecting from view, you cant guarantee your results to be ordered.

TBSPS
Starting Member

1 Post

Posted - 2011-10-28 : 15:51:20

In regards to creating a SQL view, the data I need is in interger fields and I need it to be varchar to be able to integrate with another database. How can convert interger to varchar in my sql view?

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 15:54:21

, CAST(intCol AS varchar) AS varchar_Int

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 00:50:00

quote:
Originally posted by TBSPS

In regards to creating a SQL view, the data I need is in interger fields and I need it to be varchar to be able to integrate with another database. How can convert interger to varchar in my sql view?

integer is compatible with varchar so it will undergo implicit conversion if you're comparing it to varchar, but its always worth explicitly doing conversion.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-03 : 08:22:03

quote:
Originally posted by X002548

, CAST(intCol AS varchar) AS varchar_Int

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Always specify the length when casting to character datatypes
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources