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. Some others are not used in C# but may be familiar to Visual Basic or Pascal programmers.

Comparison Operators
 

Introduction

A comparison operator is used to compare one value to another. Normally, from your knowledge of C#, 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 C# except the inequality <>. 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 void Form1_Load(object sender, System.EventArgs e)
{
	DataTable tblStudents = this.dsROSH1.Tables["Students"];
	DataColumnCollection colStudents = tblStudents.Columns;

	for(int i = 0; i < colStudents.Count; i++)
		this.cboColumns.Items.Add(colStudents[i].ColumnName);

	this.cboColumns.SelectedIndex = 0;
	this.cboSortOrder.SelectedIndex = 0;

	this.sqlDataAdapter1.Fill(this.dsROSH1);
			 
	colSelected = new DataColumn();
			 
	for(int i = 0; i < colStudents.Count; i++)
		this.cboFilterFor.Items.Add(colStudents[i].ColumnName);
}
  3. Return to the form and click contextMenu1
  4. In the menu, double-click Filter By Selection and implement its event as follows:
      
    private void mnuFiltBySel_Click(object sender, System.EventArgs e)
{
	DataGridCell curCell = this.dataGrid1.CurrentCell;

	this.dvwStudents.RowFilter = colSelected.ColumnName +
		" = '" + dataGrid1[curCell].ToString() + "'";
}
  5. Return to the form. In the menu, double-click Filter Excluding Selection and implement its event as follows:
      
    private void mnuFiltExclSel_Click(object sender, System.EventArgs e)
{
	DataGridCell curCell = this.dataGrid1.CurrentCell;

	this.dvwStudents.RowFilter = colSelected.ColumnName +
		" <> '" + dataGrid1[curCell].ToString() + "'";
}
  6. Below the form, double-click the most left combo box and implement its SelectedIndexChanged event as follows:
      
    private void cboFilterFor_SelectedIndexChanged(object sender, System.EventArgs e)
{
	string strColSelected = this.cboFilterFor.Text;

	DataRowCollection colRows = this.dsROSH1.Tables["Students"].Rows;
			 
	this.cboRecValue.Items.Clear();

	this.cboRecValue.Items.Add("NULL");
	for(int i = 0; i < colRows.Count; i++)
	{
		string strRecValue = (colRows[i])[strColSelected].ToString();

		if( strRecValue == "" )
										 continue;
		if( this.cboRecValue.Items.Contains(strRecValue) )
			continue;
		this.cboRecValue.Items.Add(strRecValue);
	}

	this.cboOperator.SelectedIndex = 0;
}
  7. Return to the form. Below the form, double-click the most right combo box and implement its SelectedIndexChanged event as follows:
      
    private void cboRecValue_SelectedIndexChanged(object sender, System.EventArgs e)
{
	string strColSelected  = this.cboFilterFor.Text;
	string strOperSelected = this.cboOperator.Text;
	string strRecValue     = this.cboRecValue.Text;

	if( strColSelected == "" )
		return;

	string strOperator = "=";
	if( strOperSelected == "<> Not Equal" )
		strOperator = "<>";
	else if( strOperSelected == "< Less Than" )
		  strOperator = "<";
	else if( strOperSelected == "<= Less Than or Equal To" )
		 strOperator = "<=";
	 else if( strOperSelected == "> Greater Than" )
		 strOperator = ">";
	 else if( strOperSelected == ">= Greater Than or Equal To" )
		 strOperator = ">=";

	if( strRecValue == "NULL" )
	   this.dvwStudents.RowFilter = "IsNull(" + strColSelected + ", 'Null Column') = 'Null Column'";
	else
this.dvwStudents.RowFilter = String.Concat(strColSelected, " ", strOperator, " '",  strRecValue, "'");

Text = String.Concat("ROSH - Student Analysis: ", this.dvwStudents.Count.ToString(), " Records Found"); 
}
  8. Return to the form. Below the form, double-click the middle combo box and implement its SelectedIndexChanged event as follows:
      
    private void cboOperator_SelectedIndexChanged(object sender, System.EventArgs e)
{
	// This event should work only if the used has already selected
	// a column in the most right combo box
	if( this.cboRecValue.Text == "" )
		return;

	cboRecValue_SelectedIndexChanged(sender, e);
}
  9. Execute the application
 
Previous Copyright © 2005-2016, FunctionX Next