create a view that return data and its count of description

I have a view that has a link to 3 tables.
I need to dispaly only the data in one table and list how
many that data is defined in one table.
here is the code for the first view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
All I need is the oilName and its COUNT of cautions.
let say the oil name is "Basil" and it has three counts.
All I need is Basil in one coumn and in the other coulmn
I need "3"
Thank youWithout DDL, I'm taking some guesses, but try:
SELECT o.oilName, count(*) AS CautionCount
FROM Oils AS o
join OilCautions AS oc
on o.OILID = oc.OILID
group by o.oilName
HTH
Vern
>--Original Message--
>I have a view that has a link to 3 tables.
>I need to dispaly only the data in one table and list how
>many that data is defined in one table.
>here is the code for the first view.
>USE Aromatherapy
>GO
>if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
>= 'V')
> DROP VIEW
> Oils_Cautions_View
>go
>CREATE VIEW Oils_Cautions_View AS
>SELECT o.oilID, oilName,
>Description FROM Oils AS o, Cautions as c, OilCautions as
>oc
>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>All I need is the oilName and its COUNT of cautions.
>let say the oil name is "Basil" and it has three counts.
>All I need is Basil in one coumn and in the other coulmn
>I need "3"
>Thank you
>.
>|||Hi Vern,
Thank you very much for your time
Cristian
>--Original Message--
>Without DDL, I'm taking some guesses, but try:
>SELECT o.oilName, count(*) AS CautionCount
>FROM Oils AS o
>join OilCautions AS oc
> on o.OILID = oc.OILID
>group by o.oilName
>HTH
>Vern
>>--Original Message--
>>I have a view that has a link to 3 tables.
>>I need to dispaly only the data in one table and list
how
>>many that data is defined in one table.
>>here is the code for the first view.
>>USE Aromatherapy
>>GO
>>if exists (select name from sysobjects
>> where name = 'Oils_Cautions_View' and
type
>>= 'V')
>> DROP VIEW
>> Oils_Cautions_View
>>go
>>CREATE VIEW Oils_Cautions_View AS
>>SELECT o.oilID, oilName,
>>Description FROM Oils AS o, Cautions as c, OilCautions
as
>>oc
>>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>>All I need is the oilName and its COUNT of cautions.
>>let say the oil name is "Basil" and it has three counts.
>>All I need is Basil in one coumn and in the other
coulmn
>>I need "3"
>>Thank you
>>.
>.
>

Create a user defined function in a user agnostic way

All,
I have two functions where one calls the other:
-- simplified example
create function plus(@.a as integer, @.b as integer)
returns integer
begin
return @.a + @.b
end
go
create function t(@.a as integer)
returns integer
begin
return cr_user.plus(10, @.a)
end
go
Is there any way to create those without explicitely mentioning the
database user ("cr_user" in this case)? Ideally I would like to put them
into a file and execute it with osql and those functions are created for
the current user. Until now I could not find a working placeholder to use
in "t" that will call the current user's "plus". (SUSER_SNAME does not
work.)
The only alternative seems to be ATM to always create functions for dbo
which is always defined.
Any ideas? TIA
Kind regards
robertWhy would you need functions that are owned by specific users? Does the
purpose of these functions depend on individual users?
Make it a practice to always qualify object names with the owner.
ML|||ML wrote:
> Why would you need functions that are owned by specific users?
You can only use functions with the owner as prefix and other parts of the
software insert the user as prefix when generating queries.

> Does
> the purpose of these functions depend on individual users?
No, other than that it is only used by a single user.

> Make it a practice to always qualify object names with the owner.
Why? To avoid name clashes? I prefer the unqualified version. It avoids
a lot of hassle and we don't have to take care of name clashes.
Regards
robert|||"Name clashes"?
There are no name clashes if common objects have the default owner (dbo). If
you created these obviously common functions with a qualified name (using db
o
as the owner), you could prevent any problems.
The pros of using qualified names are simply too numerous to mention here.
In SQL 2005 the owner is replaced by a more powerful quaifier - the schema -
maybe you should look into it to better understand its purpose.
I'm not criticizing your standards, but maybe they aren't as perferct as
you'd like to believe.
Please explain with an example why you need to create objects for every
user, when all of the objects are equal in purpose? And what do you mean by
"only used by a single user"? Is this a security issue?
ML|||ML wrote:
> "Name clashes"?
> There are no name clashes if common objects have the default owner
> (dbo).
But there would be if multiple objects with the same name but different
owners existed. I assumed you were referring to this situation when
promoting qualified names.

> If you created these obviously common functions with a
> qualified name (using dbo as the owner), you could prevent any
> problems.
> The pros of using qualified names are simply too numerous to mention
> here.
Impressive. Care to reveal some detail here? All I found so far was
faster access to execution plans and better reuse of execution plans of
stored procedures when called with qualified names.

> In SQL 2005 the owner is replaced by a more powerful quaifier - the
> schema - maybe you should look into it to better understand its
> purpose.
I might if I have the time. But ATM we're on 2k so I wouldn't want to
complicate things by mixing in concepts we can't use anyway. :-)

> I'm not criticizing your standards, but maybe they aren't as perferct
> as you'd like to believe.
I'm far from believing they are perfect. They just exist. And there is
some effort attached to changing them. Hence my conservatism. :-)

> Please explain with an example why you need to create objects for
> every user, when all of the objects are equal in purpose? And what do
> you mean by "only used by a single user"? Is this a security issue?
I don't create the same objects for every user. There is just one (1)
database user (apart from dbo). All tables, views, procedures and
functions are created unqualified and thus belong to this single user.
I'd like to stick with that simple policy. Unfortunately you can create
functions unqualified but you have to invoke them qualified. I don't see
why this is or has to be but apparently it's the way things are. Do you
have an idea why they did it this way?
I'll check whether the privileges our app requires are sufficient to
create the function for user dbo. That seems the second best alternative.
Hm, it seems creating is ok but invoking is prohibited. Darn. Seems like
I have to go down the preprocessing road.
Cheers
robert|||Ok, simply put most benefits of using qualified names are related to
performance. As you've mentioned. Another might be context of use, yet in
your case I see no need for that, since you in fact deal with a single user
(owner). In a multi role environment it could provide more clarity if method
s
that basically serve the same purpose for a variety of roles need to provide
special context-related features, which can be simplified using different
owners while keeping one name. E.g. a procedure to get product details might
return a different result for owner Engineering and a different one for owne
r
Marketing.
If you are using a single owner, why not use dbo?
But on the other hand, your only issue is with invoking functions, so either
way using a qualified name is mandatory. Simply because MS SQL Server says
so. :)
ML|||ML wrote:
> Ok, simply put most benefits of using qualified names are related to
> performance. As you've mentioned. Another might be context of use,
> yet in your case I see no need for that, since you in fact deal with
> a single user (owner). In a multi role environment it could provide
> more clarity if methods that basically serve the same purpose for a
> variety of roles need to provide special context-related features,
> which can be simplified using different owners while keeping one
> name. E.g. a procedure to get product details might return a
> different result for owner Engineering and a different one for owner
> Marketing.
Ok, thanks for clarifying.

> If you are using a single owner, why not use dbo?
I would but it seems the permissions we require for the DB user are
sufficient to create a function with owner dbo but not to invoke it later.
For the time being I'll stick with the preprocessing (i.e. the actual user
name is injected before SQL is executed).

> But on the other hand, your only issue is with invoking functions, so
> either way using a qualified name is mandatory. Simply because MS SQL
> Server says so. :)
Yep. Still I don't have an idea why this is so - I suspect the words
"historic" and "legacy" would be used in an explanation... :-)
Again, thanks for helping out!
Kind regards
robert

counting number of specific rows in SQL

hi,

I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.

Thanks,

Dror.

I don't think a SQL Server function is the best option for you. If you are returning this to SSRS, you'll probably want to leverage a stored procedure. Still, here is a generic representation of what you're asking for:

create function dbo.MyFunction( @.MyParam varchar(10) )

returns @.ret table (MyCount int, MyAvg float)

begin

insert into @.ret (MyCount, MyAvg)

select count(*), avg(MyField) from MyTable where MyOtherField=@.MyParam

return

end

This function returns a table. To retrieve data, you will call it as follows:

select *

from dbo.MyFunction( @.MyParam)

Again, I don't think this is the best approach to this problem. There are more easily read and consumed approaches to this problem.

Thanks,
Bryan

|||I am aware of the fact I can perform the same in VB - even eassier, but there is a reason for using function on the server. Supposed I want to set the number of rows to a variable, how do I do it ?|||

A VB function is not a good choice either. Are you using this data in SSRS?

Thanks,
Bryan

|||

Hi Bryan,

To tell the truth - I do not even know what SSRS is...I am new to this field, still learning. I tried to minimize my application for security (I'm affraid of paople will restore my code and see my logic creating the code), so some of the important stuff will perform as stored procedures on the SQL (which I'm also learning now...) Now I managed to calculate the average, and count the rows. But my function needs to return the result in a variable: the first variable - numbers of rows, the second - the average of a column, and the third - I need to find the fifth biggest value in a specific column. The prolem is assigning the results into variables.

Thanks again for your help,

Dror.

|||

Try something like this with appropriate substitutions. Please note, the data types I'm using for the parameters does not imply that these would be the approapriate data types for your application.

Code Snippet

create proc MyProc @.MyParam varchar(10), @.MyCount int OUTPUT, @.MyAvg float OUTPUT

as

select @.MyCount = count(*), @.MyAvg = avg(MyField)

from MyTable

where MyOtherField = @.MyParam

return 0

end

You can look up more info on using output parameters by reading the Books Online entry for the CREATE PROCEDURE statement.

Thanks,
Bryan Smith

|||thank you very much for your great help Bryan.