Easy aggregation question

Given this table
CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10), Operator
char(2))
which has multiple rows for each ExpSetNo, I want to find each ExpSetNo that
has only one TableAlias among all of the same ExpSetNo rows and which has
'CC' as the Operator in all those rows.
I would only want to return ExpSetNo 1 from this data:
INSERT SearchFieldDetail
SELECT 1, 'COMCF', 'CC'
UNION
SELECT 1, 'COMCF', 'CC'
UNION
SELECT 2, 'COMCF', 'CC'
UNION
SELECT 2, 'COC', '='
UNION
SELECT 3, 'COC', 'CC'
UNION
SELECT 3, 'COMCF', 'CC'
And here's what I'm doing.
SELECT ExpSetNo
FROM SearchFieldDetail
GROUP BY ExpSetNo
HAVING COUNT(TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0 ELSE 1
END) = 0
DROP TABLE SearchFieldDetail
This works, but are there any suggestions for improvement?> I want to find each ExpSetNo that
> has only one TableAlias among all of the same ExpSetNo rows
In this case, you need to add DISTINCT to your COUNT function like the
example below. Also, I believe your sample data insert query needs to
specify UNION ALL rather than UNION.
SELECT ExpSetNo
FROM SearchFieldDetail
GROUP BY ExpSetNo
HAVING COUNT(DISTINCT TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0
ELSE 1
END) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:xqadnVdqk_RebmvcRVn-sg@.sti.net...
> Given this table
> CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10),
> Operator
> char(2))
> which has multiple rows for each ExpSetNo, I want to find each ExpSetNo
> that
> has only one TableAlias among all of the same ExpSetNo rows and which has
> 'CC' as the Operator in all those rows.
> I would only want to return ExpSetNo 1 from this data:
> INSERT SearchFieldDetail
> SELECT 1, 'COMCF', 'CC'
> UNION
> SELECT 1, 'COMCF', 'CC'
> UNION
> SELECT 2, 'COMCF', 'CC'
> UNION
> SELECT 2, 'COC', '='
> UNION
> SELECT 3, 'COC', 'CC'
> UNION
> SELECT 3, 'COMCF', 'CC'
> And here's what I'm doing.
> SELECT ExpSetNo
> FROM SearchFieldDetail
> GROUP BY ExpSetNo
> HAVING COUNT(TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0 ELSE 1
> END) = 0
> DROP TABLE SearchFieldDetail
> This works, but are there any suggestions for improvement?
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eCvRsJ1AFHA.2104@.TK2MSFTNGP14.phx.gbl...
> In this case, you need to add DISTINCT to your COUNT function like the
> example below. Also, I believe your sample data insert query needs to
> specify UNION ALL rather than UNION.
> SELECT ExpSetNo
> FROM SearchFieldDetail
> GROUP BY ExpSetNo
> HAVING COUNT(DISTINCT TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN
0
> ELSE 1
> END) = 0
Excellent, Dan - thanks. A good example of bad test data (created without
UNION ALL) giving misleading results.

Dynamically assigning a value using the "Top N" Clause

I've developed a proc that takes an input parameter @.TopN Int. I want to
use it to dynamically pull the top n records from my DB as
such
Select TOP @.TopN UserID, Metric1, Metrics2...
Order By Metric1
I can only get this to work if I use an integer constant.
i.e. Select TOP 10 UserID
I Can't get it to work with the variable I'm passing in
Anyone know how to do this easily?
Thanks in advance
Message posted via http://www.webservertalk.comYou can use SET ROWCOUNT instead, but make sure that your ORDER BY
clause includes a key that is unique in the result set because SET
ROWCOUNT has no equivalent of the TOP WITH TIES option. Unless the
ORDER BY criteria is unique you may get unpredictable results.
David Portas
SQL Server MVP
--|||See:
http://groups.google.ca/groups?selm...FTNGP10.phx.gbl
Anith|||T Harris via webservertalk.com wrote:
> I've developed a proc that takes an input parameter @.TopN Int. I want
> to use it to dynamically pull the top n records from my DB as
> such
> Select TOP @.TopN UserID, Metric1, Metrics2...
> Order By Metric1
> I can only get this to work if I use an integer constant.
> i.e. Select TOP 10 UserID
> I Can't get it to work with the variable I'm passing in
> Anyone know how to do this easily?
> Thanks in advance
You'll need to wait for SQL 2005 for that.
David Gugick
Imceda Software
www.imceda.com|||Thanks, everyone for the responses.
Since the subset of data that I'm querying for is dynamic itself (i.e. I
don't know what the topN data set will be until after the order by is
applied) I can't directly apply the set rowcount. So the only way I can get
it to work it to query the dataset and then use the set rowcount against
the already ordered data set. This adds some overhead but it does work.
Tharrris
Message posted via http://www.webservertalk.com

Dynamic WHERE operator based on user input

Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements:

DropDownList1: This drop down list contains 3 choices- "=", ">", and "<".

Age: This text box is where someone would enter a number.

Button1: This is the form's submit button.

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error:

"The variable name'@.Age' has already been declared. Variable names must be unique within a query batch or stored procedure."

Any help would be appreciated.

Here is what I'm using in my code behind:

protected void Button1_Click(object sender, EventArgs e)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT * FROM People WHERE Age ");
switch (DropDownList1.SelectedValue)
{
case "=":
sb.Append("= ");
break;
case ">":
sb.Append("> ");
break;
case "<":
sb.Append("< ");
break;
}
sb.Append("@.Age");
SqlDataSource1.SelectCommand = sb.ToString();
SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text);
}

i would create 3 stored procedures first with "<" second with "=" and third with ">"

then, in code behind

if (DropDownList1.SelectedValue.ToString() == "<") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("LessThan_toate", conn);//cnn = your conn string command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader;// your dataReader DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() =="=") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("EqualsWith", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() ==">") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("GreatherThen", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }

sure, my stored procedures doesn't have input parameters but you can change easly that

|||

adammckee:

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

So basically you want everything in the table? what are you excluding?

|||

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||Either I don't understand what you're doing or you're making it more complicated than it should be. Wouldn't this work?

Private Sub cmd_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cmd.ClickDim sQueryAs String sQuery ="SELECT * FROM People WHERE Age " &Me.DropDownList1.SelectedValue &" " &Me.txtAge.Text SqlDataSource1.SelectCommand = sQueryEnd Sub

|||

This worked nicely, but doesn't this leave me open to SQL injection? I was attempting to do this with the SelectParameters method to avoid that.

To clarify what I'm trying to do for the other person that asked, I want to give the user the ability to search for people in the database based on their age. If the user wants to search for anyone that's over 50, they pull down the drop down list, select the ">" operator then type "50" in the text field and perform the search.

ecbruck:

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||

If you're worried about it, then go ahead and use a Parameter instead. However, I would at least recommend declaratively placing the Parameter within your SqlDataSource on your ASPX page and then simply setting the DeefaultValue of the property within your Click event handler or the SqlDataSource.Selecting event handler.

|||

For anyone interested, this is what works for me. Thanks again for the advice.

1protected void Button1_Click(object sender, EventArgs e)2 {3 System.Text.StringBuilder sb =new System.Text.StringBuilder();4 sb.Append("SELECT * FROM People WHERE Age ");5switch (DropDownList1.SelectedValue)6 {7case"=":8 sb.Append("= ");9break;10case">":11 sb.Append("> ");12break;13case"<":14 sb.Append("< ");15break;16 }17 sb.Append("@.Age");18 SqlDataSource1.SelectCommand = sb.ToString();19if (SqlDataSource1.SelectParameters["Age"] ==null)20 SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, TextBox1.Text);21else22 SqlDataSource1.SelectParameters["Age"].DefaultValue = TextBox1.Text;23 }

Dynamic statement in variable - parseerror

I am trying to use this statement in a variable, including another variable:

"SELECT * FROM my_table WHERE CAST([timestamp] AS INT) > " + @.[User::LastTimestamp]

But the variable value insists on giving me this error:

The expression for variable "VariableName" failed evaluation. There was an error in the expression.

I cast the columntype "timestamp" to int, and the variable "LastTimestamp is stored as int32, and has a default value of 0. I simply can't grasp what it is I am missing.

Is it because the expression is part string and part integer? If so, how is that avoided?

Thanks in advance

Ohh, also, I have tried with (DT_STR) @.[User::LastTimestamp], but that doesnt work either|||

Casting to a string should fix the problem.

This works for me-

"SELECT * FROM my_table WHERE CAST([timestamp] AS INT) > " + (DT_WSTR,10)@.[User::LastTimestamp]

dynamic sql in Cursor

a table named tbl_customerMaster

create table(customerID int, customerName varChar(50), Adderss varChar(100))

---

create procedure sp_saTest ( @.customerID varChar(20) )

AS

declare curCustomer cursor

for SELECT customerName, Adderss FROM tbl_customerMaster WHERE customerID IN (@.customerID)

open curCustomer

fatch....

....

xyz, xyz, close cursor

---

the sp created successfully, but when i try to execute that sp i found a error there

Exec sp_saTest (1,2,5,7,10)

Server: Msg 245, Level 16, State 1, Procedure sp_Customer
Syntax error converting the varchar value '1,2,5,7,10' to a column of data type int.

what is the problem there?

suppose when i fire as follow

SELECT customerName, Adderss FROM tbl_customerMaster WHERE customerID IN (1,2,5,7,10)

directly in it will be work properly, then why there is error with parameter name?

i m using SQL Server 2000.

The problem is that you're passing in a string, when it wants a list of numbers. I know the string happens to look like a list of numbers, but it's actually not one.

You could do something like:

WHERE ',' + @.customerID + ',' like '%,' + cast(customerID as varchar(20)) + ',%'

But this is kinda nasty. Another alternative is to split the string into a series of numbers. There is code for this at
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt

It's down near there end, an example of just this type of behaviour.

Hope this helps...

Rob

Dynamic SQL Help needed.

I have the following stored procedure :

CREATE PROCEDURE usp_CraneRental_UpdateRate
@.iEntryID int,
@.cField sysname,
@.cValue varchar(100),
@.dtModified datetime OUTPUT

AS

SELECT @.dtModified = GetDate()

DECLARE @.cSql VARCHAR(500)

SELECT @.cSql = 'UPDATE tbCraneRentalRates SET ' + @.cField + ' = ' + RTRIM(@.cValue) +
', Modified = ' + '"' + CAST(@.dtModified AS VARCHAR) + '"' +
' WHERE EntryID = ' + CAST(@.iEntryID AS VARCHAR)

EXEC(@.cSql)
IF(@.@.ERROR <> 0 OR @.@.ROWCOUNT <= 0)
RAISERROR('Failed to update transportation rate!',16,1)
GO

that generates the following SQL String :

UPDATE tbCraneRentalRates SET MoveIn = 0, Modified = "May 20 2004 9:59 AM" WHERE EntryID = 1

The error I am getting is:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'May 20 2004 9:59AM'.

???
This doesn't make sence, the SQL statement looks perfectly fine ?

Any help?
Mike Btry using 2 ' quotes to make it create ', youre giving it the text in speach " " marks which sql doesn't like.

try '' should make it display '|||put this before the create statement in qa:

set quoted_identifiers off|||try using 2 ' quotes to make it create ', youre giving it the text in speach " " marks which sql doesn't like.

try '' should make it display '

That works, thanks.

Mike B