Having clause without GROUP BY clause?

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.those queries actually run in oracle? i rather doubt it

without a GROUP BY, the entire table is considered a single group

the individual col2 values would not necessarily all be the same, therefore the HAVING condition in the first query would not necessarily give you the results you want, assuming it even runs, which i doubt

in the second query you will surely get a syntax error even in oracle

perhaps what you want for the two queries is:

SELECT SUM(col1) from test where col2 < 5

SELECT SUM(col1) from test WHERE x=y AND col2 < 5|||Sorry for the typos. Actually the queries are as follows.

SELECT SUM(col1) from test HAVING SUM(col2) < 5

SELECT SUM(col1) from test WHERE x=y HAVING SUM(col2) < 5

Thanks,
Gopi.|||do you have sql server? if so, why don't you test those queries and see what you get

handling parameters that could be missing

I have a search app that I'm developing in ASP.NET
It has 5 parameters
MRN, Name, Sex, DOB, SSN
All or any combination can be used.
what is the best way to handle the non-supplied parameters?
i.e. if the user selects only supplies 'Name' and 'MRN'
I want the SQL for the stored procedure to read as if it were just 2
parameters:
(but have one stored procedure that has parameter holders for all 5)
Select MRN, Name, Sex, DOB, SSN
From Membership
WHERE MRN = 123456 AND Name like 'Johns%'
The way I have been handling this is to rewrite a different SQL statement
for every possible situation, and pass that string to the adapter. But I ca
n
see that I'm not taking advantage of the processing that SQL server could do
in a stored procedure, and for 1.4 Million records, my app is just timing ou
t
all the time.
This must be a common issue. Could someone show me the ideal way to handle
this?Have a look at
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"jonefer" <jonefer@.discussions.microsoft.com> wrote in message
news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
> can
> see that I'm not taking advantage of the processing that SQL server could
> do
> in a stored procedure, and for 1.4 Million records, my app is just timing
> out
> all the time.
> This must be a common issue. Could someone show me the ideal way to
> handle
> this?
>|||"jonefer" wrote:
> I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
can
> see that I'm not taking advantage of the processing that SQL server could
do
> in a stored procedure, and for 1.4 Million records, my app is just timing
out
> all the time.
> This must be a common issue. Could someone show me the ideal way to handl
e
> this?
Have you tried something along the line of:
create procedure p_MyProc
@.pMyParm1 int = NULL, @.pMyParm2 datetime = NULL,
@.pMyParm3 varchar(128) = NULL, @.pMyParm4 bit = NULL
as
if @.pMyParm1 IS NULL do something ...
if @.pMyParm2 IS NULL do something ...
if @.pMyParm3 IS NULL do something ...
if @.pMyParm4 IS NULL do something ...|||Thank you. That was excellent.
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/dyn-search.html
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jonefer" <jonefer@.discussions.microsoft.com> wrote in message
> news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>
>|||I implemented my search with the 5 parameters using your Dynamic SQL example
.
But because of the way I want to present the final results... it always
times out.
So I will list what I'm trying to get in my final results as well as what
the Stored procedure looks like (perhaps you can help me tweak it)
What I want in the final selection:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;
How I implemented this in the Stored Procedure:
CREATE PROCEDURE qMemberSelect
@.MRN int = NULL,
@.MemNAME nvarchar(40) = NULL,
@.DOB datetime = NULL,
@.SSN nvarchar(9) = NULL,
@.SEX nvarchar(1) = NULL,
@.debug bit = 0 AS
DECLARE @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql =
'SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP
],[SGR],[FROM-DT],[THRU-DT] FROM
MEMBERSHIP AS Y
WHERE 1=1'
if @.MRN IS NOT NULL
SELECT @.sql = @.sql + ' AND MRN = @.xMRN'
if @.MemNAME IS NOT NULL
SELECT @.sql = @.sql + ' AND MemName like @.xMemNAME + ''%'''
if @.DOB IS NOT NULL
SELECT @.sql = @.sql + ' AND DOB = @.xDOB'
if @.SSN IS NOT NULL
SELECT @.sql = @.sql + ' AND SSN = @.xSSN'
if @.SEX IS NOT NULL
SELECT @.sql = @.sql + ' AND SEX = @.xSEX'
if @.debug = 1
PRINT @.sql
SELECT @.sql = @.sql + ' GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR,
[FROM-DT], [THRU-DT]
HAVING len([THRU-DT])= 0
Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
Membership As X Where X.MRN = Y.MRN And X.MRN
Not in(Select Z.MRN From Membership As Z Where len(Z.[THRU-DT])=0))
ORDER BY MemNAME, [FROM-DT] DESC'
SELECT @.paramlist = '@.xMRN int,
@.xMemName nvarchar(40),
@.xDOB datetime,
@.xSSN nvarchar(9),
@.xSEX nvarchar(1)'
EXEC sp_executesql @.sql, @.paramlist,
@.MRN, @.MemNAME, @.DOB, @.SSN, @.SEX
But there must be a way to do this in two phases because trying it all in
one pull is just not efficient.
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/dyn-search.html
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jonefer" <jonefer@.discussions.microsoft.com> wrote in message
> news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>
>|||How about...
SELECT MRN, Name, Sex, DOB, SSN
FROM dbo.Membership
WHERE (MRN = @.MRN or @.MRN is null)
AND (Name like @.Name or @.Name is null)
AND (Sex = @.Sex or @.Sex is null)
etc.
TIP: If there are not user specific tables, always specify the owner of the
object so that it is not ambiguous and SQL Server does not have to check for
<userid>.Membership and if not found, check for dbo.Membership. This applie
s
for referencing tables, views, and stored procedures. Also, never prefix a
stored procedure name with sp_ or xp_ as that can cause unexpected side
effects.
Just my two cents,
Joe
"jonefer" wrote:

> I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
can
> see that I'm not taking advantage of the processing that SQL server could
do
> in a stored procedure, and for 1.4 Million records, my app is just timing
out
> all the time.
> This must be a common issue. Could someone show me the ideal way to handl
e
> this?
>|||Joe
If you use sp_XXXXXX SQL server looks into MAster database first even if you
mentioned the database.sp_XXX... So, we are looking unnecessarily in master
database . that is why most folks suggest do not use sp_XXXX .I never heard
of side effects... If you know any please share with us.
--
SQL SERVER DBA
"Joe from WI" wrote:
> How about...
> SELECT MRN, Name, Sex, DOB, SSN
> FROM dbo.Membership
> WHERE (MRN = @.MRN or @.MRN is null)
> AND (Name like @.Name or @.Name is null)
> AND (Sex = @.Sex or @.Sex is null)
> etc.
> TIP: If there are not user specific tables, always specify the owner of th
e
> object so that it is not ambiguous and SQL Server does not have to check f
or
> <userid>.Membership and if not found, check for dbo.Membership. This appl
ies
> for referencing tables, views, and stored procedures. Also, never prefix
a
> stored procedure name with sp_ or xp_ as that can cause unexpected side
> effects.
> Just my two cents,
> Joe
> "jonefer" wrote:
>