|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
More Efficient SQL Statement - Select Count(1)I know that you can write select statement (Select Count(1)) within Oracle
if finds one record it will stop the search critieria. Please help me modify the SQL statement listed below to use the equivalent if it finds one records it stops and output is 1 if not the output is 0. Thank You, SET @COUNT_CALLS_REC_1 = (Select count(Icent_Num) from TKCalls.dbo.tblCalls where DATEDIFF(mi, StartedTime, GETDATE()) <=30 AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) Preface the query with a return limitation -ROWCOUNT.
USE Northwind GO SET ROWCOUNT 0 SELECT CustomerID FROM Customers WHERE Country = 'Mexico' SET ROWCOUNT 0 -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Joe K." <Joe K*@discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-25436FBEC8C9@microsoft.com... > > I know that you can write select statement (Select Count(1)) within Oracle > if finds one record it will stop the search critieria. > > Please help me modify the SQL statement listed below to use the equivalent > if it finds one records it stops and output is 1 if not the output is 0. > > Thank You, > > > SET @COUNT_CALLS_REC_1 = > (Select count(Icent_Num) > from TKCalls.dbo.tblCalls > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) Sorry, the first SET ROWCOUNT should be 1 -NOT 0.
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Arnie Rowland" <ar***@1568.com> wrote in message news:%23ojjUjHnGHA.2264@TK2MSFTNGP04.phx.gbl... Preface the query with a return limitation -ROWCOUNT.USE Northwind GO SET ROWCOUNT 0 SELECT CustomerID FROM Customers WHERE Country = 'Mexico' SET ROWCOUNT 0 -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam Show quote "Joe K." <Joe K*@discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-25436FBEC8C9@microsoft.com... > > I know that you can write select statement (Select Count(1)) within Oracle > if finds one record it will stop the search critieria. > > Please help me modify the SQL statement listed below to use the equivalent > if it finds one records it stops and output is 1 if not the output is 0. > > Thank You, > > > SET @COUNT_CALLS_REC_1 = > (Select count(Icent_Num) > from TKCalls.dbo.tblCalls > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) something like this?
if exists( Select 1 from TKCalls.dbo.tblCalls where DATEDIFF(mi, StartedTime, GETDATE()) <=30 AND left(cast(Icent_Num as varchar(20)),6) = ('962472') ) SET @COUNT_CALLS_REC_1 = 1 else SET @COUNT_CALLS_REC_1 = 0 Joe k.,
Use the operator "exists" and do not manipulate the columns in the "where" clause as possible, to let SQL Server to use the indexes optimally in case they exists. if exists ( select * from TKCalls.dbo.tblCalls where StartedTime between dateadd(minute, -30, GETDATE()) and GETDATE() and cast(Icent_Num as varchar(20)) like '962472%' ) set ... else set ... go AMB Show quote "Joe K." wrote: > > I know that you can write select statement (Select Count(1)) within Oracle > if finds one record it will stop the search critieria. > > Please help me modify the SQL statement listed below to use the equivalent > if it finds one records it stops and output is 1 if not the output is 0. > > Thank You, > > > SET @COUNT_CALLS_REC_1 = > (Select count(Icent_Num) > from TKCalls.dbo.tblCalls > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) Are you sure that on Oracle, the statement SELECT COUNT(1) will never
return a value > 1? I would be very surprised if this is the Oracle behavior, because that is not what the SQL statement is specifying... Gert-Jan Joe K. wrote: Show quote > > I know that you can write select statement (Select Count(1)) within Oracle > if finds one record it will stop the search critieria. > > Please help me modify the SQL statement listed below to use the equivalent > if it finds one records it stops and output is 1 if not the output is 0. > > Thank You, > > SET @COUNT_CALLS_REC_1 = > (Select count(Icent_Num) > from TKCalls.dbo.tblCalls > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) doesn't "select top 1 * from ..." work for you?
José. Gert-Jan Strik wrote: Show quote > Are you sure that on Oracle, the statement SELECT COUNT(1) will never > return a value > 1? I would be very surprised if this is the Oracle > behavior, because that is not what the SQL statement is specifying... > > Gert-Jan > > > Joe K. wrote: > > > > I know that you can write select statement (Select Count(1)) within Oracle > > if finds one record it will stop the search critieria. > > > > Please help me modify the SQL statement listed below to use the equivalent > > if it finds one records it stops and output is 1 if not the output is 0. > > > > Thank You, > > > > SET @COUNT_CALLS_REC_1 = > > (Select count(Icent_Num) > > from TKCalls.dbo.tblCalls > > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 > > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) As I recall, one of the issues with TOP 1 is that it has to find all records
(or at least indexes) first in order to determine which one is the TOP 1. So that may not be 'efficient'. ;-) -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam <josearau***@gmail.com> wrote in message news:1151700301.773682.267740@75g2000cwc.googlegroups.com... doesn't "select top 1 * from ..." work for you?José. Gert-Jan Strik wrote: Show quote > Are you sure that on Oracle, the statement SELECT COUNT(1) will never > return a value > 1? I would be very surprised if this is the Oracle > behavior, because that is not what the SQL statement is specifying... > > Gert-Jan > > > Joe K. wrote: > > > > I know that you can write select statement (Select Count(1)) within > > Oracle > > if finds one record it will stop the search critieria. > > > > Please help me modify the SQL statement listed below to use the > > equivalent > > if it finds one records it stops and output is 1 if not the output is 0. > > > > Thank You, > > > > SET @COUNT_CALLS_REC_1 = > > (Select count(Icent_Num) > > from TKCalls.dbo.tblCalls > > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 > > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) > As I recall, one of the issues with TOP 1 is that it has to find all records (or at least indexes) Only with ORDER BY. Without ORDER BY, you ask for any one row, so the optimizer and engine know it > first in order to determine which one is the TOP 1. So that may not be 'efficient'. can stop after the first row it encounters. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Arnie Rowland" <ar***@1568.com> wrote in message news:%23G0c2mInGHA.3388@TK2MSFTNGP05.phx.gbl... > As I recall, one of the issues with TOP 1 is that it has to find all records (or at least indexes) > first in order to determine which one is the TOP 1. So that may not be 'efficient'. > > ;-) > -- > Arnie Rowland, YACE* > "To be successful, your heart must accompany your knowledge." > > *Yet Another Certification Exam > > > <josearau***@gmail.com> wrote in message > news:1151700301.773682.267740@75g2000cwc.googlegroups.com... > doesn't "select top 1 * from ..." work for you? > > José. > > Gert-Jan Strik wrote: >> Are you sure that on Oracle, the statement SELECT COUNT(1) will never >> return a value > 1? I would be very surprised if this is the Oracle >> behavior, because that is not what the SQL statement is specifying... >> >> Gert-Jan >> >> >> Joe K. wrote: >> > >> > I know that you can write select statement (Select Count(1)) within Oracle >> > if finds one record it will stop the search critieria. >> > >> > Please help me modify the SQL statement listed below to use the equivalent >> > if it finds one records it stops and output is 1 if not the output is 0. >> > >> > Thank You, >> > >> > SET @COUNT_CALLS_REC_1 = >> > (Select count(Icent_Num) >> > from TKCalls.dbo.tblCalls >> > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 >> > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) > > In case of an ORDER BY, it depends on the columns in the ORDER BY. If
they match an index, then the optimizer might still be able to stop searching after finding the first entry. Gert-Jan Tibor Karaszi wrote: Show quote > > > As I recall, one of the issues with TOP 1 is that it has to find all records (or at least indexes) > > first in order to determine which one is the TOP 1. So that may not be 'efficient'. > > Only with ORDER BY. Without ORDER BY, you ask for any one row, so the optimizer and engine know it > can stop after the first row it encounters. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > "Arnie Rowland" <ar***@1568.com> wrote in message news:%23G0c2mInGHA.3388@TK2MSFTNGP05.phx.gbl... > > As I recall, one of the issues with TOP 1 is that it has to find all records (or at least indexes) > > first in order to determine which one is the TOP 1. So that may not be 'efficient'. > > > > ;-) > > -- > > Arnie Rowland, YACE* > > "To be successful, your heart must accompany your knowledge." > > > > *Yet Another Certification Exam > > > > > > <josearau***@gmail.com> wrote in message > > news:1151700301.773682.267740@75g2000cwc.googlegroups.com... > > doesn't "select top 1 * from ..." work for you? > > > > José. > > > > Gert-Jan Strik wrote: > >> Are you sure that on Oracle, the statement SELECT COUNT(1) will never > >> return a value > 1? I would be very surprised if this is the Oracle > >> behavior, because that is not what the SQL statement is specifying... > >> > >> Gert-Jan > >> > >> > >> Joe K. wrote: > >> > > >> > I know that you can write select statement (Select Count(1)) within Oracle > >> > if finds one record it will stop the search critieria. > >> > > >> > Please help me modify the SQL statement listed below to use the equivalent > >> > if it finds one records it stops and output is 1 if not the output is 0. > >> > > >> > Thank You, > >> > > >> > SET @COUNT_CALLS_REC_1 = > >> > (Select count(Icent_Num) > >> > from TKCalls.dbo.tblCalls > >> > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 > >> > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) > > > > True.
Thanks or catching that, Gert-Jan. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message news:44A64D5A.B3629004@toomuchspamalready.nl... > In case of an ORDER BY, it depends on the columns in the ORDER BY. If > they match an index, then the optimizer might still be able to stop > searching after finding the first entry. > > Gert-Jan > > > Tibor Karaszi wrote: >> >> > As I recall, one of the issues with TOP 1 is that it has to find all records (or at least >> > indexes) >> > first in order to determine which one is the TOP 1. So that may not be 'efficient'. >> >> Only with ORDER BY. Without ORDER BY, you ask for any one row, so the optimizer and engine know >> it >> can stop after the first row it encounters. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> "Arnie Rowland" <ar***@1568.com> wrote in message news:%23G0c2mInGHA.3388@TK2MSFTNGP05.phx.gbl... >> > As I recall, one of the issues with TOP 1 is that it has to find all records (or at least >> > indexes) >> > first in order to determine which one is the TOP 1. So that may not be 'efficient'. >> > >> > ;-) >> > -- >> > Arnie Rowland, YACE* >> > "To be successful, your heart must accompany your knowledge." >> > >> > *Yet Another Certification Exam >> > >> > >> > <josearau***@gmail.com> wrote in message >> > news:1151700301.773682.267740@75g2000cwc.googlegroups.com... >> > doesn't "select top 1 * from ..." work for you? >> > >> > José. >> > >> > Gert-Jan Strik wrote: >> >> Are you sure that on Oracle, the statement SELECT COUNT(1) will never >> >> return a value > 1? I would be very surprised if this is the Oracle >> >> behavior, because that is not what the SQL statement is specifying... >> >> >> >> Gert-Jan >> >> >> >> >> >> Joe K. wrote: >> >> > >> >> > I know that you can write select statement (Select Count(1)) within Oracle >> >> > if finds one record it will stop the search critieria. >> >> > >> >> > Please help me modify the SQL statement listed below to use the equivalent >> >> > if it finds one records it stops and output is 1 if not the output is 0. >> >> > >> >> > Thank You, >> >> > >> >> > SET @COUNT_CALLS_REC_1 = >> >> > (Select count(Icent_Num) >> >> > from TKCalls.dbo.tblCalls >> >> > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 >> >> > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) >> > >> > Thanks Guys, I learn alot hanging out with you.
-- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:O2a6zoPnGHA.3436@TK2MSFTNGP02.phx.gbl... > True. > Thanks or catching that, Gert-Jan. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message > news:44A64D5A.B3629004@toomuchspamalready.nl... >> In case of an ORDER BY, it depends on the columns in the ORDER BY. If >> they match an index, then the optimizer might still be able to stop >> searching after finding the first entry. >> >> Gert-Jan >> >> >> Tibor Karaszi wrote: >>> >>> > As I recall, one of the issues with TOP 1 is that it has to find all >>> > records (or at least indexes) >>> > first in order to determine which one is the TOP 1. So that may not be >>> > 'efficient'. >>> >>> Only with ORDER BY. Without ORDER BY, you ask for any one row, so the >>> optimizer and engine know it >>> can stop after the first row it encounters. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> >>> "Arnie Rowland" <ar***@1568.com> wrote in message >>> news:%23G0c2mInGHA.3388@TK2MSFTNGP05.phx.gbl... >>> > As I recall, one of the issues with TOP 1 is that it has to find all >>> > records (or at least indexes) >>> > first in order to determine which one is the TOP 1. So that may not be >>> > 'efficient'. >>> > >>> > ;-) >>> > -- >>> > Arnie Rowland, YACE* >>> > "To be successful, your heart must accompany your knowledge." >>> > >>> > *Yet Another Certification Exam >>> > >>> > >>> > <josearau***@gmail.com> wrote in message >>> > news:1151700301.773682.267740@75g2000cwc.googlegroups.com... >>> > doesn't "select top 1 * from ..." work for you? >>> > >>> > José. >>> > >>> > Gert-Jan Strik wrote: >>> >> Are you sure that on Oracle, the statement SELECT COUNT(1) will never >>> >> return a value > 1? I would be very surprised if this is the Oracle >>> >> behavior, because that is not what the SQL statement is specifying... >>> >> >>> >> Gert-Jan >>> >> >>> >> >>> >> Joe K. wrote: >>> >> > >>> >> > I know that you can write select statement (Select Count(1)) within >>> >> > Oracle >>> >> > if finds one record it will stop the search critieria. >>> >> > >>> >> > Please help me modify the SQL statement listed below to use the >>> >> > equivalent >>> >> > if it finds one records it stops and output is 1 if not the output >>> >> > is 0. >>> >> > >>> >> > Thank You, >>> >> > >>> >> > SET @COUNT_CALLS_REC_1 = >>> >> > (Select count(Icent_Num) >>> >> > from TKCalls.dbo.tblCalls >>> >> > where DATEDIFF(mi, StartedTime, GETDATE()) <=30 >>> >> > AND left(cast(Icent_Num as varchar(20)),6) = ('962472')) >>> > >>> > > This should work as well...
-- scott0100 ------------------------------------------------------------------------ scott0100's Profile: http://www.dbtalk.net/m491 View this thread: http://www.dbtalk.net/t316762  |
|||||||||||||||||||||||
Other interesting topics