Have Geek Brain

Best way to query database using OR operator

I am trying to create the following sql script in a subsonic query that I can use with a collection:

Select * from tableA
where tableA.InvoiceID = @Invoice
and tableA.VersionID = @VersionID
and tableA.ActiveDate >= GetDate()
and (tableB.InActiveDate is null or tableB.InActiveDate <= GetDate())

Here is my current code:

orders = new OrdersCollection();
query = new SubSonic.Query(Tables.Orders);
query.WHERE("InvoiceID", Invoice.InvoiceID);
query.AND("VersionID", version.VersionID);
query.AND("ActiveDate", SubSonic.Is.LessThanOrEqualTo(System.DateTime.Now.ToString()).Value);
query.AND("InActiveDate", SubSonic.Comparision.Is, null).OR("InActiveDate", SubSonic.Is.GreaterThanOrEqualTo("System.DateTime.Now.ToString()).Value);

query.CommandTimeOut = intSubSonicTimeOut; partXrefColl.Load (FilePartXref.FetchByQuery (query));

The problem I think I am running into is that it dates dates and an OR statement. I tried this with only InvoiceID and VersionID and I am returning the data back to the collection.

Any help would be greatly appreciated.

0


a source to share


6 answers


You SQL specified a less than or equal to (<=) operator, but you specified GreaterThanOrEqualTo (). Could there be a problem with reversing the logic?



0


a source


Sorry the correct syntax for the SQL query should have been:

Select * from tableA
where tableA.InvoiceID = @Invoice
and tableA.VersionID = @VersionID
and tableA.ActiveDate <= GetDate()
and (tableB.InActiveDate is null or tableB.InActiveDate >= GetDate())

And the code should have been:



orders = new OrdersCollection();
query = new SubSonic.Query(Tables.Orders);
query.WHERE("InvoiceID", Invoice.InvoiceID);
query.AND("VersionID", version.VersionID);
query.AND("ActiveDate", SubSonic.Is.LessThanOrEqualTo(System.DateTime.Now.ToString()).Value);
query.AND("InActiveDate", SubSonic.Comparision.Is, null).OR("InActiveDate", SubSonic.Is.GreaterThanOrEqualTo("System.DateTime.Now.ToString()).Value);

orders.Load (Invoices.FetchByQuery (query);

Sorry for the confusion.

0


a source


Wild guess:

"SubSonic.Comparision.Is, null" does not map to "IS NULL", instead it maps to "= NULL"

0


a source


Assuming you are using SubSonic 2.1 or higher, you can write this query as:

OrdersCollection orders = DB.Select.From(Orders.Schema)
  .Where(Orders.Columns.InvoiceID).IsEqualTo(1)
  .And(Orders.Columns.VersionID).IsEqualTo(1)
  .And(Orders.Columns.ActiveDate).IsLessThanOrEqualTo(DateTime.Now)
  .AndExpression(Orders.Columns.InActiveDate).IsNull()
  .Or(Orders.Columns.InActiveDate).IsGreaterThanOrEqualTo(DateTime.Now)
  .ExecuteAsCollection<OrdersCollection>();

The ANDExpression will cause it to be nested after it

0


a source


Adam has it for sure - what you are missing is the Expression part, which is given using the AndExpression

0


a source


After a little debugging, I was able to execute the query like this:

 OrderColl =  DB.Select().From(Orders.Schema)
                    .Where(Orders.Columns.VersionID).IsEqualTo(version.VersionID)
                    .And(Orders.Columns.FulfillNumber).IsEqualTo(invoice.InvocieID)
                    .And(Orders.Columns.ActiveDate).IsLessThanOrEqualTo(DateTime.Now)
                    .AndExpression(Orders.Columns.InactiveDate).IsNull()
                    .Or("InActiveDate").IsGreaterThanOrEqualTo(DateTime.Now)
                    .ExecuteAsCollection<OrdersCollection>();

Thanks everyone for your help.

0


a source






More articles:


All Articles