Home

Criteria For Data Analysis
 

 

Introduction

Besides selecting and sorting records, data analysis also consists of restricting the list of records to examine at one time. To do this, you would include a clause in your SQL statement to create a particular. The interpreter would select only records that abide by the rule of your choice. The rule used to restrict the list of available records is called a criterion. The plural is criteria but it can also be used in singular.

WHERE is Data

To set a criterion in your SQL statement, you use the WHERE keyword in the following formula:

SELECT What FROM TableName WHERE Condition

The What and the TableName factors follow the same rules we have applied so far.

The Condition factor is used to specify the rule applied to restrict the list of records. A condition is formulated as a logical expression that produces a Boolean result. To create an expression, you use comparison operators supported by the SQL. You are already familiar with some of the operators.

Comparison Operators
 

Introduction

A comparison operator is used to compare one value to another. Normally, from your knowledge of Visual Basic .NET, you should already be familiar with all the routine comparisons performed on values and variables. Most operators used in SQL for comparison are the same as those used in Visual Basic .NET. Based on this, you can perform your comparisons on column names and constant values.

If you are using .NET Framework classes of the System.Data.SqlTypes namespace in your code, each one of its classes has an overloaded method that corresponds to each comparison operator we will review.

Equal =

The equality operator is used to compare one value to another. For a WHERE condition, this operator can be used to compare the values of a column to a particular value for a match. The condition would have the following formula:

ColumnName = 'Value'

In this case, you can specify a name for the column that holds the type of value you want to use as a criterion. Once you have identified the column, you can assign it the particular value of its records that would excluding non-abiding columns. For example, imagine you have a list of business customers in different countries, to get a list of only the customers who live in Mexico, you would set the criterion as Country = 'Mexico'. Here is an example of such a statement:

If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an Equals() method overloaded with two versions. The first corresponds to the routine Equals() method that all .NET Framework class inherit from the Object class. The second version allows you to compare, for equality, two columns created with the same data type.

 

Comparison Operators: Not Equal <>

To find out if two fields hold different values, you can use the inequality operator which is represented by <>. Its syntax is:

Value1 <> Value2

This comparison is performed between Value1 and Value2. If they hold different values, then the comparison produces a TRUE value. If they hold the same value, the comparison produces FALSE. This shows that the equality (=) and the inequality (<>) operators are opposite each other.

Here is an example:

SELECT FirstName, LastName, Gender
FROM Students
WHERE Gender <> 'Female';

If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an NotEquals() method used to compare, for inequality, two columns created with the same data type.
 

Comparison Operators: Less Than <

The "Less Than" operator uses the following formula:

Value1 < Value2

If Value1 holds a value that is lower than that of Value2, the comparison produces TRUE. If Value1 holds a value that is greater than or similar to that of Value2, the comparison renders FALSE. You can formulate the condition as follows:

SELECT     FirstName, LastName, Gender, DateOfBirth
FROM        Students
WHERE     DateOfBirth < '1988-06-01'

Here is an example:

If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an LessThan() method used to compare two columns created with the same data type. The value of the first argument to this method is compared with the value of the second argument. If the first value is lower than the second, this method returns true. Otherwise, it returns false.

Comparison Operators: Less Than or Equal <=

When comparing two values, you may want to know whether two fields hold the same value or if one is lower than the other. This comparison can be performed with the "Less Than Or Equal To" operator. It is represented by <= and its formula is:

Value1 <= Value2

If both operands (Value1 and Value2) hold the same value, then the comparison produces a TRUE result. If Value1 holds a value that is lower than that of Value2, the comparison still produces a TRUE result. By contrast, if the value of Value1 is higher than that of Value2, the comparison renders a FALSE result. Notice that the > and the <= operators are opposite each other.

Here is an example:

SELECT FirstName, LastName, DateOfBirth, EmailAddress
FROM Students
WHERE DateOfBirth <= '1990-01-01';

If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an LessThanOrEqual() method used to compare two columns created with the same data type. The value of the first argument to this method is compared with the value of the second argument. If the first value is lower than the second or both are equal, this method returns true. If the value of the first argument is strictly less than that of the second, this method returns false.
 

Comparison Operators: Greater Than >

The > operator is used to find out whether one value is "Greater Than" another. Its syntax is:

Value1 > Value2

The operation is performed on the values of Value1 and Value2. If Value1 holds a value greater than that of Value2, then the comparison produces TRUE. Otherwise, the comparison produces FALSE. That is, if the value of Value2 is greater than or equal to that of Value1, then the comparison produces FALSE.

If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an GreaterThan() method used to compare two columns created with the same data type. The value of the first argument to this method is compared with the value of the second argument. If the first value is higher than the second, this method returns true. Otherwise, it returns false.

 

Comparison Operators: Greater Than or Equal >=

If you have two values and want to find out whether they hold similar values or the first is greater than the second, you can use the >= operator whose syntax is:

Value1 >= Value2

If both Value1 and Value2 hold the same value, then the comparison renders a TRUE result. Similarly, if the left operand, Value1, holds a value greater than that of the right operand, Value2, the comparison still produces TRUE. If the value of Value1 is less than the value of Value2, the comparison produces a FALSE result. Therefore, < and >= are opposite.

If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an LessThanOrEqual() method used to compare two columns created with the same data type. The value of the first argument to this method is compared with the value of the second argument. If the first value is higher than the second or both are equal, this method returns true. If the value of the first argument is strictly higher than that of the second, this method returns false.

Practical Learning Practical Learning: Filtering Records
  1. In the bottom section of the form, add the following controls:
     
    Control Name Text Items Other Properties
    Label   Filter For:   Anchor: Bottom, Left
    ComboBox cboFilterFor     Anchor: Bottom, Left
    DropDownStyle: DropDownList
    ComboBox cboOperator   = Equals
    <> Not Equal
    < Less Than
    <= Less Than or Equal To
    > Greater Than
    >= Greater Than or Equal To    		 Anchor: Bottom, Left
    DropDownStyle: DropDownList
    ComboBox cboRecValue     Anchor: Bottom, Left
    DropDownStyle: DropDownList
  2. Double-click an unoccupied area of the form and change the Load event as follows:
      
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
			Handles MyBase.Load
        Dim tblStudents As DataTable = DsROSH1.Tables("Students")
        Dim colStudents As DataColumnCollection = tblStudents.Columns
        Dim i As Integer

        For i = 0 To colStudents.Count - 1 Step 1
            cboColumns.Items.Add(colStudents(i).ColumnName)
        Next

        cboColumns.SelectedIndex = 0
        cboSortOrder.SelectedIndex = 0

        SqlDataAdapter1.Fill(DsROSH1)

        colSelected = New DataColumn

        For i = 0 To colStudents.Count - 1 Step 1
            cboFilterFor.Items.Add(colStudents(i).ColumnName)
        Next
End Sub
  3. In the Class Name combo box, select mnuFiltBySel
  4. In the Method Name combo box, select Click
  5. Implement the event as follows:
      
    Private Sub mnuFiltBySel_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles mnuFiltBySel.Click
        Dim curCell As DataGridCell = dataGrid1.CurrentCell

        dvwStudents.RowFilter = colSelected.ColumnName & _
         " = '" & CStr(dataGrid1(curCell)) & "'"
End Sub
  6. In the Class Name combo box, select mnuFiltExclSel
  7. In the Method Name combo box, select Click
  8. Implement the event as follows:
      
    Private Sub mnuFiltExclSel_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles mnuFiltExclSel.Click
        Dim curCell As DataGridCell = dataGrid1.CurrentCell

        Text = CStr(dataGrid1(curCell))
        dvwStudents.RowFilter = colSelected.ColumnName & _
         " <> '" & CStr(dataGrid1(curCell)) & "'"
End Sub
  9. In the Class Name combo box, select cboFilterFor
  10. In the Method Name combo box, select SelectedIndexChanged
  11. Implement the event as follows:
      
    Private Sub cboFilterFor_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles cboFilterFor.SelectedIndexChanged
        Dim strColSelected As String = cboFilterFor.Text
        Dim colRows As DataRowCollection = DsROSH1.Tables("Students").Rows
        Dim i As Integer

        cboRecValue.Items.Clear()

        cboRecValue.Items.Add("NULL")
        For i = 0 To colRows.Count - 1 Step 1
            Dim strRecValue As String = colRows(i).Item(strColSelected).ToString()

            cboRecValue.Items.Add(strRecValue)
        Next

        cboOperator.SelectedIndex = 0
End Sub
  12. In the Class Name combo box, select cboRecValue
  13. In the Method Name combo box, select SelectedIndexChanged
  14. Implement the event as follows:
      
    Private Sub cboRecValue_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles cboRecValue.SelectedIndexChanged
        Dim strColSelected As String = cboFilterFor.Text
        Dim strOperSelected As String = cboOperator.Text
        Dim strRecValue As String = cboRecValue.Text

        If strColSelected = "" Then
            Exit Sub
        End If

        Dim strOperator As String = "="
        If strOperSelected = "<> Not Equal" Then
            strOperator = "<>"
        ElseIf strOperSelected = "< Less Than" Then
            strOperator = "<"
        ElseIf strOperSelected = "<= Less Than or Equal To" Then
            strOperator = "<="
        ElseIf strOperSelected = "> Greater Than" Then
            strOperator = ">"
        ElseIf strOperSelected = ">= Greater Than or Equal To" Then
            strOperator = ">="
        End If

        If strRecValue = "NULL" Then
            dvwStudents.RowFilter = "IsNull(" & strColSelected & " & 'Null Column') = 'Null Column'"
        Else
            dvwStudents.RowFilter = strColSelected & " " & strOperator & " '" & strRecValue & "'"

            Text = "ROSH - Student Analysis: " & CStr(dvwStudents.Count) & " Records Found"
        End If
End Sub
  15. In the Class Name combo box, select cboOperator
  16. In the Method Name combo box, select SelectedIndexChanged
  17. Implement the event as follows:
      
    Private Sub cboOperator_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles cboOperator.SelectedIndexChanged
        ' This event should work only if the user has already selected
        ' a column in the most right combo box
        If cboRecValue.Text = "" Then
            Exit Sub
        End If

        cboRecValue_SelectedIndexChanged(sender, e)
End Sub
  18. Execute the application
 
Previous Copyright © 2005-2016, FunctionX Next