Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Building a desktop application using C#.net and arcobjects. I'm trying to retrieve records from a shape file using IQueryFilter. Having trouble writing the where clause in my scenario.

The scenario is as follows.

I want to only retrieve records that are invalid and fall out side the below condition . The category field in the sample data should be matched with the category field in the "condition" and check the Limits in the "condition" with either "Limit-From" or "Limit-To" field. Either one of the "Limit-From" or "Limit-To" fields should match the limit in the condition. Also both the "Limit-From" and "Limit-To" fields can match the limits in the "Condition ".( when both match also that is not an error).

I have inserted a comments field for each record stating the reason for validity/invalidity. According to the sample data, as I want only invalid records, I want just record 6 and 7 as the output from the query.

Any help in writing the where clause in the query will be greatly appreciated. (I need only the invalid records. So in this case it should be just 6 and 7)

alt text

alt text

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

If the number of individual categories is fairly low (you have 3 now) something along those lines would be both simple and quite efficent:

WHERE NOT
(
  (LimitFrom > 130 and Category = 1)
  or (LimitTo > 130 and Category = 1)
  or (LimitFrom >= 101 and LimitFrom <= 130 and Category = 2)
  or (LimitTo >= 101 and LimitTo <= 130 and Category = 2)
  or (LimitFrom >= 91 and LimitFrom <= 100 and Category = 3)
  or (LimitTo >= 91 and LimitTo <= 100 and Category = 3)
)

Tested with your sample data on Oracle, should work virtually everywhere since it's standard SQL.

share|improve this answer
    
Thanks a lot for the answer. Works great. I was trying with between but it failed. The tutorial shows otherwise; edndoc.esri.com/arcobjects/9.2/NET/… –  user1506 Dec 28 '10 at 21:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.