how to group condition?

Sep 13, 2011 at 2:13 PM

hi, All, 

I want to generate the filter condition like 'A or ( B And C ) Or ( D and E ), how can I generate the group condition?

Coordinator
Sep 20, 2011 at 6:29 AM

You can use the DBComparison static methods and then combine with the DBBooleanOp.
Below is an example that I've spilt into parameters, comparisons, and then the query.
You could combine all this into one statement, but it would be unreadable.

//Create the parameters
DBParam mindatevalue = DBParam.ParamWithValue(new DateTime(2010,1,1));
DBParam maxdatevalue = DBParam.ParamWithValue(new DateTime(2011,1,1));
DBParam customer = DBParam.ParamWithValue(1);

//I've split out the comparisons for ease or reading
DBComparison cid = DBComparison.Compare(DBField.Field("CustomerID"), Compare.Equals, customer);
DBComparison maxdate = DBComparison.Compare(DBField.Field("OrderDate"), Compare.LessThan, maxdatevalue);
DBComparison mindate = DBComparison.Compare(DBField.Field("OrderDate"), Compare.GreaterThan, mindatevalue);
DBComparison nulldate = DBComparison.Compare(DBField.Field("OrderDate"), Compare.Is, DBConst.Null());

//Use the DBBooleanOp to join multiple clauses.
DBQuery sel = DBQuery.SelectAll().From("Orders")
                                 .Where(cid)
                                    .And(DBBooleanOp.Compare(nulldate, BooleanOp.Or,
                                         DBBooleanOp.Compare(mindate, BooleanOp.And, maxdate)));

And this is the sql it generates along with the parameter values (for MySql).


SELECT *
	FROM `Orders`
	WHERE  ( (`CustomerID` = ?_param1)  AND  ( (`OrderDate` IS NULL)  OR  ( (`OrderDate` > ?_param2)  AND  (`OrderDate` < ?_param3) ) ) ) ;
Parameter : ?_param1 ( Type:Int32, Direction:Input, Size:0, Source:, Value:1)
Parameter : ?_param2 ( Type:DateTime, Direction:Input, Size:0, Source:, Value:01/01/2010 00:00:00)
Parameter : ?_param3 ( Type:DateTime, Direction:Input, Size:0, Source:, Value:01/01/2011 00:00:00)

Hope this helps

Sep 20, 2011 at 12:13 PM

Thank you very much, it is very great!

Dec 9, 2014 at 10:37 PM
Hi,

This is almost exactly what i was looking for....however how do you build up the 2nd part of the where clause dynamically within a loop?

I'm looking for something like this....

Select * from tbl_test where ((id =1) AND (col1 = A OR col2 = B AND col3 = C ............AND col N = X ))

But i'd want the col1, col2, col3, col N clauses stored in a table somewhere and then looped around.

Thanks
Danial