Im trying build a query from c# like below, but its not work, how
should I wrote MAX section?

int xValue = someIntValue;
string sql;
sql   = "SELECT xmin, xmax, ymin, ymax FROM COORDS ";
sql += "WHERE MAX(xmax, " + xValue.ToString() + ")" >= 100";

What does your query mean?  As it is written, it is missing
a GROUP BY, or it has too many columns in the SELECT
list.  Can you explain in words what rows you want?  Better
yet, give sample data for COORDS and a reasonable value
of xValue, and show what you want?

You can find the max() of two columns with this

"case when xmax > " +  xValue.ToString() + " then xmax
else " + xValue.ToString()

but there may still be a problem in your query.

Steve Kass
Drew University

bge***@gmail.com wrote:

Show quoteHide quote

there isn't function you seek. MAX (although simililar name) is in SQL an
aggregate function and has usage far from you need.

1) you can use something like:

SELECT * FROM ta WHERE ( ta.col1 >= 100 ) OR (ta.col2 >= 100) OR (....)

to "simulate" C#'s max function.

2) in your case you can use

if (xValue >= 100) {
sql = "SELECT xmin, xmax, ymin, ymax FROM COORDS" } else {
sql = "SELECT xmin, xmax, ymin, ymax FROM COORDS WHERE xmax>=100" };

which should be doing the same thing.
Exactly in your example isn't really good idea using WHERE MAX(column,
CONSTANT) >= CONSTANT, it must be done for every row in table COORDS and if
your xValue >= 100 then it is always TRUE. So all the WHERE clause is only
slowing down SQL server

3) I think you can also write your own function (like fn_max) and link it to
SQL as UDF (.dll). But I don't know how do it under MSSQL.

Show quoteHide quote

First of all, you should avoid building dynamic SQL (Google "SQL
injection"), so the parameterized query should look something like:

SELECT xmin, xmax, ymin, ymax FROM COORDS
WHERE CASE WHEN xmax > @xValue THEN xmax ELSE @xValue END >= 100


and xValue should be sent as a parameter, for example:

command.Parameters.Add("@xValue", xValue)


I'm not really sure what you're trying to accomplish with this code,
however.  Please explain.

<bge***@gmail.com> wrote in message
Show quoteHide quote

Very thanks for answers.
Actually, the example what I wrote is not exactly what i want. I was
only try to simplify problem to focus MAX case.
Im sorry for confused minds.

Let me explain my problem exactly.

I have a table that contains geometrical objects. xmin ,xmax etc.
fields are the bounding box of the object. In my code, i have a
rectangle and just want to select objects who intersect with this
rectangle. The query runs on single table and there is no another
table. The table have 5 fields ("xmin", "ymin", "xmax", "ymax" and
binary "points" field)

Lets look at full version of my code.

int my_xmin, my_ymin, my_xmax, my_ymax; // These variables represents
bounding box of my rectangle

string sql = "SELECT points FROM MyTable " +
sql += "WHERE ";
sql += "MAX(xmax, " + my_xmax + ") - MIN(xmin, " + my_xmin+ ") <= (xmax
- xmin) + " + (my_xmax - my_xmin)+ " AND";
sql += "MAX(ymax, " + my_ymax + ") - MIN(miny, " + my_ymin+ ") <= (ymax
- ymin) + " + (my_ymax - my_ymin);

I know MAX and MIN will not help me, i only visualize my thoughts in
this way
I think using CASE in query will solve my problem but i didn't try yet.
Here is my question: Is this approach true to find intersection? and
using CASE in query is the only way?

Best regards.

Deleting large amount of data on sql server 2000
Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC
grouping and showing concatenated varchar column?