Need help in creatin a 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

Need help in creatin a view

Author

Topic

shamunda
Starting Member

9 Posts

Posted - 2008-04-10 : 14:15:21

Hello

I have this query for MS SQL server:

SELECT [UserTable].LastName, [UserTable].FirstName,
CEILING(sum([history_usage].session_time)/CAST(60000 as FLOAT))as 'Time Spent(MIN)'

FROM ZSRIVENDEL.dbo.UserTable, ZSRIVENDEL.dbo.history_usage
WHERE [UserTable].student_id = [history_usage].student_id AND (([UserTable].ClientID='URTU001')
AND ([UserTable].GroupID=3400) AND ([history_usage].time_stamp>=? AND [history_usage].time_stamp<?) AND ([history_usage].session_time<21600000))
GROUP BY [UserTable].LastName,[UserTable].FirstName
ORDER BY [UserTable].FirstName

When I run it from SQL server it pops up a window asking for two vales:

Start Date: 03/1/2008
End Date: 04/1/2008

then returns the results:

lastName firstName timeSpent
-------- --------- ---------
Schmo Joe 52

So from query analyzer it works fine. However when I paste this query into the window for creating a 'View' it won't allow me to use this query.

Could someone tell me what i need to modify in order to get this to work as a view?

Thanks you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-10 : 14:51:25

Show us how did you create so we can help

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 14:52:20

a popup? are you sure you are using sql server?

elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 15:06:07

Views do not accept parameters (using ?)
You could write this as a table-valued function (which accepts parameters).

E 12°55'05.25"
N 56°04'39.16"

shamunda
Starting Member

9 Posts

Posted - 2008-04-10 : 15:38:11

I'm sorry I should have been more precise....

I'm using my Toad for SQL server as my profiler.

Peso:"Views do not accept parameters (using ?)
You could write this as a table-valued function (which accepts parameters)".

Ah a little closer to my solution...But being a newbie (really newbie) i have no idea how that would be done.

Could you give me an example?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 15:43:24

First read Books Online about table-valued function.
And then, if you still have questions, I can answer them.

E 12°55'05.25"
N 56°04'39.16"

shamunda
Starting Member

9 Posts

Posted - 2008-04-10 : 16:03:44

Are table-valued functions that same to User Defined Functions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:11:31

Yes. User Defined Functions can be of two types.

1) Scalar-value functions.
2) Table-value functions.

All this and more, are printed in Books Online.
Please have a look and entertain yourself for the next hour or so.

E 12°55'05.25"
N 56°04'39.16"

shamunda
Starting Member

9 Posts

Posted - 2008-04-10 : 16:37:41

Well unfortunately i'm trying to design something as proof of concept for a basic reporting application in my company. There have been far too many times when my ideas have help forward the careers of others so just this once I would like to take credit for something I actually came up with and created.

I've found a few good examples that explain the User Defined Functions and a very good example using Northwind.

a simple example for northwind i found was:

declare @Return varchar(30)
select @Return = case @Country
when ' Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canda' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europ'
else 'Unknown'
end

return @return

-------
I tried to related that to some code i found on our spreadsheet (EXCEL):

SELECT [UserTable].LastName, [UserTable].FirstName,
CEILING(sum([history_usage].session_time)/CAST(60000 as FLOAT))as 'Time Spent(MIN)'

FROM ZSRIVENDEL.dbo.UserTable, ZSRIVENDEL.dbo.history_usage
WHERE [UserTable].student_id = [history_usage].student_id AND (([UserTable].ClientID='URTU001')
AND ([UserTable].GroupID=3400)
AND ([history_usage].time_stamp>=? AND [history_usage].time_stamp<?)
AND ([history_usage].session_time<21600000))
GROUP BY [UserTable].LastName,[UserTable].FirstName
ORDER BY [UserTable].FirstName
------

And the farthest i've gotten so far is:

declare @startDate DATETIME
declare @endDate DATETIME

I'm not asking anyone to write of the rest of the code that I need to create. This is just a very small portion of a full blown reporting system that i'm designing in .net. That I can manage. But I'm asking someone to help me take that small snippet of code and put it into a 'function' if it needs to go there, or a 'view'...etc...

then from those examples I can probably extended the rest on my own.

Thanks
Sham

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:48:06

[code]CREATE FUNCTION dbo.fnMyFunction
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS TABLE
AS
BEGIN
RETURN (
SELECT ut.LastName,
ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = 'URTU001'
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName,
ut.FirstName
ORDER BY ut.FirstName
)
END[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:48:42

[code]CREATE PROCEDURE dbo.uspMyProcedure
(
@StartDate DATETIME,
@EndDate DATETIME
)
AS

SET NOCOUNT ON

SELECT ut.LastName,
ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = 'URTU001'
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName,
ut.FirstName
ORDER BY ut.FirstName[/code]

E 12°55'05.25"
N 56°04'39.16"

shamunda
Starting Member

9 Posts

Posted - 2008-04-10 : 17:05:21

Excellent!!

Thanks Peso!!!

Unlikely to gain fame with the company :P however should it become of anything i'll sneak in some credit your way :)

-Shamunda

shamunda
Starting Member

9 Posts

Posted - 2008-04-10 : 17:28:20

Peso:

Thanks for all your help and as promised I was able to work through some other things on my own.

For example when I tried saving your Function i was presented with

"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified"

So did some googling, read some documentation added a

SELECT TOP 99.999 PERCENT ...

at the top (no pun intended), and the Function was saved. Not sure if that's correct but it seems to have saved the function :)

-Sham

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 17:37:39

don't put the ORDER BY in the function or view. take out that TOP 99.999 percent nonsense. put the ORDER BY in the query that targets the TVF or view.

the optimizer reserves the right to ignore ORDER BY if it's in a tvs or view definition.

see: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

elsasoft.org

shamunda
Starting Member

9 Posts

Posted - 2008-04-10 : 17:50:31

Damn that rocks dude...See this is what i'm talking about in terms of learning.

I like it. Ok I removed the ORDER BY stuff...and everything still works as planned. I'm still unfamiliar with how to associate this with a 'view' but I'll dig from here to see what I come up with.

thanks for all your help.

-Sham

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 18:00:29

You can JOIN and all other sorts of things (SELECTs) with a TVF as you can any other view and/or table.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources