Checking numeric values in a field and then grouping it out in the where clause

Question

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.

There are 3 attribute field called "StreetName", "TypeID", "Allowed" in the shape file. The "StreetName" field can have street names such as "Main Avenue", "Robert street", "5th Street" , "ST 6N","CR 7" etc. I need to only retrieve the streets which have a number in it. ( so in this case it will be just "5th Street" , "ST 6N","CR 7") . Once the streets with numbers are retrieved I need to group it according to the "TypeID" field. The "TypeID" field is of type double. Then I need to check for a given "TypeID" whether all the records in the "Allowed" field is the same. ( The "Allowed" field just has 2 different values either Yes or No) If there are records that are not equal in the "Allowed" field for a given "TypeID" I need to get those as the query result.

Example values from the attribute table. ( Record Number column is added for illustration purposes only)

So according to the example data, record 4 ( which has a Yes in the "Allowed" field where all the others of that "TypeID" has No) and record 9 ( which has a No in the "Allowed" field where all the others of that TypeID has Yes) should be the result of the query.

I can select the numbered streets by using a where clause like this.

IQueryFilter spatFilt;

spatFilt.WhereClause = "ST_NAME LIKE '%1%' OR ST_NAME LIKE '%2%' OR ST_NAME LIKE '%3%' OR ST_NAME LIKE '%4%' OR ST_NAME LIKE '%5%' OR ST_NAME LIKE '%6%' OR ST_NAME LIKE '%7%' OR ST_NAME LIKE '%8%' OR ST_NAME LIKE '%9%' ";

After that I'm stuck on how to group according to TypeID and then select the invalid records..

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 4 and 9)

Answer 1

Found a way out. I combined(concatenated the values) the two columns "StreetName" and "TypeID" into a new field called StreetNameTypeID and populated it with the concatenated values.

Had the same where clause above to get the streets with numeric values.

Then used the following logic where I compare the StreetNameTypeID and the Allowed fields.

                    string strStreetFeat = "";
                    string strDirectionOnSign = "";
                    string strComments="Error"
                    strStreetFeat = pFeature_Str.get_Value(intStreetNameTypeID).ToString();
                    strDirectionOnSign = pFeature_Str.get_Value(intAllowed).ToString();
                    while (pFeature_Str != null)
                    {
                        pFeature_Str = StreetCursor.NextFeature();                            
                        if (strStreetFeat == pFeature_Str.get_Value(intStreetNameTypeID).ToString() && strDirectionOnSign != pFeature_Str.get_Value(intAllowed).ToString())
                        {
                            pFeature_Str.set_Value(pTable_Str.Fields.FindField("Comment"), strComments);
                            pFeature_Str.Store();
                            intNumberofErrors++;
                        }
                        if (strStreetFeat != pFeature_Str.get_Value(intStreetNameTypeID).ToString() )
                        {
                            strStreetFeat = pFeature_Str.get_Value(intStreetNameTypeID).ToString();
                            strDirectionOnSign = pFeature_Str.get_Value(intAllowed).ToString();


 }
                        }