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
Showing posts with label integer. Show all posts
Showing posts with label integer. Show all posts
Tuesday, March 27, 2012
create a unique Random integer
Hi there,
I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key.
I have written the following function, but when i call it as a default value for a field, it does not produce a unique number.
CREATE FUNCTION GETNEXTID(@.CURDATE DATETIME)
RETURNS BIGINT
AS
BEGIN
RETURN (SELECT CAST(
CAST(DATEPART(YY,@.CURDATE) AS VARCHAR) +
RIGHT('0' + CAST(DATEPART(M,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(D,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(HH,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(SS,@.CURDATE) AS VARCHAR),2) +
RIGHT('00' + CAST(DATEPART(MS,@.CURDATE) AS VARCHAR),3) AS BIGINT))
END
Can anyone help?I would create a table as follows:
Create Table DatabaseId
(
DBId Int Identity,
DBName vachar (40) NOT NULL
)
When you import data from the different databases, prefix your unique identifier with DBId. That should give you an unique number across the DB's.|||Take a look at newid()
I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key.
I have written the following function, but when i call it as a default value for a field, it does not produce a unique number.
CREATE FUNCTION GETNEXTID(@.CURDATE DATETIME)
RETURNS BIGINT
AS
BEGIN
RETURN (SELECT CAST(
CAST(DATEPART(YY,@.CURDATE) AS VARCHAR) +
RIGHT('0' + CAST(DATEPART(M,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(D,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(HH,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(SS,@.CURDATE) AS VARCHAR),2) +
RIGHT('00' + CAST(DATEPART(MS,@.CURDATE) AS VARCHAR),3) AS BIGINT))
END
Can anyone help?I would create a table as follows:
Create Table DatabaseId
(
DBId Int Identity,
DBName vachar (40) NOT NULL
)
When you import data from the different databases, prefix your unique identifier with DBId. That should give you an unique number across the DB's.|||Take a look at newid()
Friday, February 24, 2012
covert from string to integer
Are there any function to conver a string to integer?
like it can convert a string "16" to a number 16=CInt("16") should work
or
=Int32.Parse("16")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:eE3Z0DQnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Are there any function to conver a string to integer?
> like it can convert a string "16" to a number 16
>
like it can convert a string "16" to a number 16=CInt("16") should work
or
=Int32.Parse("16")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:eE3Z0DQnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Are there any function to conver a string to integer?
> like it can convert a string "16" to a number 16
>
Sunday, February 19, 2012
counting with different conditions
Hi, I am unable to find a solution for this which I initially thought
would be easy
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
RiyazOn 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com|||Life Saver.
> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver.
[vbcol=seagreen]
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert|||I'm just know basic SQL. Can u show me how to use CASE?
> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:[vbcol=seagreen]
> I'm just know basic SQL. Can u show me how to use CASE?
>
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>
>|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
>
would be easy
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
RiyazOn 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com|||Life Saver.
> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver.
[vbcol=seagreen]
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert|||I'm just know basic SQL. Can u show me how to use CASE?
> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:[vbcol=seagreen]
> I'm just know basic SQL. Can u show me how to use CASE?
>
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>
>|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
>
counting with different conditions
Hi, I am unable to find a solution for this which I initially thought
would be easy :(
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
RiyazOn 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy :(
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy :(
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com|||Life Saver. :)
> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver. :)
>> select (select count(*) from tbl where n1< 1000) as condition1 ,
>> (select count(*) from tbl where n2 < 1000) as condition2,
>> (select count(*) from t where n1 < n2) as condition3
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert|||I'm just know basic SQL. Can u show me how to use CASE?
> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:
> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
>> robert
>|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
>> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
>> robert
>>
>
would be easy :(
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
RiyazOn 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy :(
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy :(
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com|||Life Saver. :)
> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver. :)
>> select (select count(*) from tbl where n1< 1000) as condition1 ,
>> (select count(*) from tbl where n2 < 1000) as condition2,
>> (select count(*) from t where n1 < n2) as condition3
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert|||I'm just know basic SQL. Can u show me how to use CASE?
> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:
> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
>> robert
>|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
>> I'm just know basic SQL. Can u show me how to use CASE?
>> Wait, 'till you see the performance of that. IMHO a solution involving
>> CASE is more efficient.
>> robert
>>
>
counting with different conditions
Hi, I am unable to find a solution for this which I initially thought
would be easy
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
Riyaz
On 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert
|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com
|||Life Saver.
> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com
|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver.
[vbcol=seagreen]
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert
|||I'm just know basic SQL. Can u show me how to use CASE?
> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert
|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:[vbcol=seagreen]
> I'm just know basic SQL. Can u show me how to use CASE?
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.
|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegro ups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>
>
|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegro ups.com...
>
would be easy
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
Riyaz
On 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert
|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com
|||Life Saver.
> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com
|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver.
[vbcol=seagreen]
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert
|||I'm just know basic SQL. Can u show me how to use CASE?
> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert
|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:[vbcol=seagreen]
> I'm just know basic SQL. Can u show me how to use CASE?
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.
|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegro ups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>
>
|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegro ups.com...
>
Subscribe to:
Posts (Atom)