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
--
Arnie 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-2
5436FBEC8C9@.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
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||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" <arnie@.1568.com> wrote in message news:%23ojjUjHnGHA.2264@.TK
2MSFTNGP04.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
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-2
5436FBEC8C9@.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'))|||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
"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:
> 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=E9.
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:
cle
ent|||This should work as well...
scott0100
---
scott0100's Profile: http://www.dbtalk.net/m491
View this thread: http://www.dbtalk.net/t316762|||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
<josearaujof@.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:|||> 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 optimi
zer 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" <arnie@.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 recor
ds (or at least indexes)
> first in order to determine which one is the TOP 1. So that may not be 'ef
ficient'.
> ;-)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <josearaujof@.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:
>