Home

Introduction to Data Filters

 

Introduction to Conditions

 

Overview

In the database world, a filter is a funel whose job is to select some records and exclude or ignore others. To indicate how that funnel should work, you formulate a condition using a Boolean operation. In data analysis, the condition is associated with a SELECT statement.

If you are working in a Query Editor or the Query Designer, you can visually create the whole SQL statement without writing a single line of code. Otherwise, you can create the statement using your knowledge of SQL.

Practical LearningPractical Learning: Introducing Data Filters

  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. In the Object Explorer, expand Databases
  4. From the previous lessons, make sure you have the Department Store1 database.
    In the Object Explorer, expand DepartmentStore1
  5. Right-click DepartmentStore1 and click New Query
  6. Expand Tables
  7. Right-click Inventory.StoreItems and click Edit Top 200 Rows
  8. On the Query Designer toolbar, click the Show Diagram Pane button Show Diagram Pane, the Show Criteria Pane button Show Criteria Pane, and the Show SQL Pane button Show SQL Pane
  9. In the SQL pane, change the statement as follows:
    SELECT ItemNumber AS [Item #],
           Manufacturer,
           Category, 
           ItemName AS [Name/Description], 
           UnitPrice AS [Unit Price]
    FROM   Inventory.StoreItems
  10. On the main menu, click Query Designer -> Execute SQL

WHERE is the Filter?

When analyzing data, you can ask the database engine to apply one or more filters. To visually create a condition:

  • In the result section of a Query Editor, after looking at the records, mentally apply the condition you want and click the gray button on the left of that row. In the following example, the user wants to select the record where the student # is 24795711:
     

    Visually Selecting Records

  • In a Query Designer, in the Criteria pane, click the box at the intersection of the Column's field and the Filter column:

Filter

In the SQL, the primary keyword used to create a condition is WHERE. A basic formula to follow is:

SELECT WhatField(s) FROM WhatObject WHERE Expression;

We are already familiar with the sections before WHERE. The Expression used in a condition is built using algebraic, logical, and string operators. The Expression is called a criterion. Although a group of expressions, making it plural, is called criteria, the word criteria is sometimes used for a singular expression also.

The Expression of a WHERE statement is written using the formula:

ColumnName Operator Value

The ColumnName must be an existing column of a table. It is followed by an appropriate operator as we will see. Value is the value that will set the condition.

To make the statement easier to read, you should include it in parentheses after WHERE.

Logical Comparisons

 

Introduction

For your databases, you can create expressions that represent a combination of values, variables, and operators. To support expressions, Transact-SQL provides various operators.

A comparison is a Boolean operation that produces a true or a false result, depending on the values on which the comparison is performed. A comparison is performed between two values of the same type; for example, you can compare two numbers, two characters, the names of two cities, the value of one column with a constant, or the values of two columns..

Equality Operator =

To compare two values for equality, use the = operator. Its formula is:

Value1 = Value2
Logical Comparisons

The equality operation is used to find out whether two values are the same. From the above formula, the SQL interpreter would compare Value1 and Value2. If Value1 and Value2 are the same, the comparison produces a TRUE result. If they are different, the comparison renders FALSE.

The equality operation can be illustrated as follows:

Equalilty Flowchart

You can perform the comparison for equality on columns that use all the data types we have seen so far but there are some things you should keep in mind:

  • When performed on natural numbers, the equality is used to find out if two numbers are exactly the same. In most cases, equality comparison on integers is the easiest and probably the most accurate
  • When the equality comparison is performed on strings, the character or symbol on the same positions of two strings would be compared. The symbols, the characters and their cases, would be compared using the rules of the language that is specified in the collation of the database
  • You should avoid performing equality comparision on floating-point numbers because you could get unpredictable results. For example, we know that 2.67 and 2.99 could be converted to 3.00 depending on how the decimal data type was set up (with or without precision and scale, and depending on the types of precision and scale). In fact, 2.49 and 2.51 could be converted differently (to 2.00 and 3.00 respectively) depending on the data type setup. From elementary or primary school, we know that 2.67 and 2.99 are not equal, which would happen if you compare the values of two columns of decimal and decimal(6.3) types
Updating Records 
  • The equality comparison on monetary values can be unpredictable or unreliable. For example, if you compare the hourly salaries of two employees, $12.50 and $12.51 are certainly different. What about yearly salaries of $48,000 and &48,250. Are they that different? If you answer Yes, imagine you write an application that performs income tax calculations at the end of the year. Is it accurate to compare the salaries of $88,250 and $88,255 for eqality? If the comparison for equality produces False, are those salaries really different?
 

Practical LearningPractical Learning: Comparing For Equality

  1. In the Criteria section, click the first box under Filter
  2. Type 81307492
  3. On the Query Designer toolbar, click the Execute button Execute
  4. Click the SQLQuery1.sql tab
  5. Type:
    USE DepartmentStore1;
    GO
    SELECT ALL * FROM Inventory.StoreItems
    WHERE Category = N'Men';
    GO
  6. To execute, press F5

Not Equal <>

As opposed to equality, to find out if two values are not equal, use the <> operator. Its formula is:

Value1 <> Value2

The <> is a binary operator (like all logical operators) proposed by the international standard (ISO). It is used to compare two values. The values can come from two variables as in Variable1 <> Variable2. Upon comparing the values, if both hold different values, the comparison produces a TRUE. Otherwise, the comparison renders FALSE or a null value.

It can be illustrated as follows:

Flowchart: Not Equal

Notice that the Not Equal operator <> is the opposite to the Equality operator =.

Besides the <> operator of the ISO SQL, Transact-SQL also supports the C language inequal operator, which is represented as !=. It essentially performs the same role as <> and can be used in the same circumstances.

The comparison for inequality follows the same concerns as equality:

  • Natural numbers provide the easiest and most reliable comparisons
  • Inequality comparison on strings follows the same rules as equality
  • Comparison for inequality on decimal values is more likely to produce True than False results
  • Have the same concerns when performing inequality comparison on monetary values as when comparing for equality

Practical LearningPractical Learning: Comparing For Inequality

  1. Click inside the Query Editor and press Ctrl + A
  2. Type:
    SELECT ALL * FROM Inventory.StoreItems
    WHERE SubCategory <> N'Shoes';
    GO
  3. To execute, press F5
  4. Click the DepartmentStore1.Inventory.StoreItems tab
  5. In the Criteria section, delete = 81307492
  6. In the Filder section, click the box corresponding to Manufacturer and type <> N'Nautica'
  7. Right-click anywhere in the window and click Execute SQL

Less Than <

To find out whether one value is lower than another, use the < operator. Its formula is:

Value1 < Value2

The value held by Value1 is compared to that of Value2. As it would be done with other operations, the comparison can be made between two variables, as in Variable1 < Variable2. If the value held by Value1 is lower than that of Value2, the comparison produces a true or positive result.

The Less Than operator "<" can be illustrated as follows:

Flowchart: Less Than

The comparison for lower value is performed differently depending on the data type:

  • Once again, natural numbers provide the easiest and most reliable comparisons
  • When the comparison for lower value is performed on strings, the first symbols or characters on both strings are comparead, using the alphabetical rules of the language used in the database (collation). Based on the alphabet, if the first letter of the first string string is (alphabetically) lower than the first letter of the second string, the comparison produces false the operaiton ends. If the symbols or characters on the same positions are the same, the comparison continues to the next symbol or character. If all symbols and characters on the same positions are the same, the comparison produces true
  • As mentioned already, avoid comparing floating-point numbers or monetary values for equality or inequality. On the other hand, comparison for lower value is certainly more accurate on decimal numbers

Practical LearningPractical Learning: Comparing For Lower Value

  1. In the Criteria section, delete <> N'Aldredge'
  2. Under Filter, click the box corresponding to UnitPrice and type < 100
  3. On the Query Designer toolbar, click the Execute button Execute

Less Than Or Equal To <=

The Equality and the Less Than operators can be combined to compare two values. This allows you to know if two values are the same or if the first is less than the second. The operator used is <= and its formula is:

Value1 <= Value2

If both Value1 and Value2 are the same, the result is true or positive. If the left operand, in this case Value1, holds a value lower than the second operand, in this case Value2, the result is still true. If the value of Value1 is strictly higher than that of Value, the comparison produces a FALSE result.

A <= operation can be illustrated as follows:

Flowchart

Practical LearningPractical Learning: Testing Lower or Equal Value

  1. In the Criteria section, change < to <=, to get <= 40
  2. On the Query Designer toolbar, click the Execute button Execute
  3. Click the SQLQuery1.sql tab
  4. Click inside the Query Editor and press Ctrl + A
  5. Type:
    SELECT ALL * FROM Inventory.StoreItems
    WHERE Category <= N'Girls';
    GO
  6. To execute, press F5

Filter

Greater Than >

To find out if one value is strictly greater than another, you can use the > operator. Its formula is:

Value1 > Value2

Both operands, in this case Value1 and Value2, can be variables or the left operand can be a variable while the right operand is a constant. If the value on the left of the > operator is greater than the value on the right side or a constant, the comparison produces a true or positive value. Otherwise, the comparison renders false or null.

The > operator can be illustrated as follows:

Greater Than

Notice that the > operator is the opposite to <=.

Practical LearningPractical Learning: Comparing For Greater Value

  1. Click the SQLQuery1.sql tab
  2. Click inside the Query Editor and press Ctrl + A
  3. Type:
    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           aparts.Available
    FROM Presentation.Units aparts
    WHERE aparts.Price > 1200
    ORDER BY [Monthly Rent], "Primary Deposit";
    GO
  4. To execute, press F5

Filter

Greater Than or Equal To >=

The greater than and the equality operators can be combined to produce an operator as follows: >=. This is the "greater than or equal to" operator. Its formula is:

Value1 >= Value2

The comparison is performed on both operands: Value1 and Value2. If the value of Value1 and that of Value2 are the same, the comparison produces a true or positive value. If the value of the left operand is greater than that of the right operand, the comparison produces true or positive also. If the value of the left operand is strictly less than the value of the right operand, the comparison produces a false or null result. This can be illustrated as follows:

Flowchart: Greater Than Or Equal To

Notice that the >= operator is the opposite to <.

Practical LearningPractical Learning: Comparing For Greater or Value

  1. Change the statement in the Query Editor as follows:
    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           aparts.Available
    FROM Presentation.Units aparts
    WHERE aparts.Price >= 1100
    ORDER BY [Monthly Rent], "Primary Deposit";
    GO
  2. To execute, press F5

Filter

Not Greater Than !>

Transact-SQL supports an operator represented as !>. This means that a value is not greater than the indicated value. In most cases, this is equivalent to the Less Than Or Equal To operator "<=".

Here is an example:

USE University3;
GO
SELECT ALL * FROM Inventory.StoreItems
WHERE LastName !> N'Alton';
GO

Not Greater Than

Not Less Than !<

Transact-SQL supports one more comparison operator represented as !<. It is used to find out whether one of the operands is less than the other. This operator is equivalent to >=.

Here is an example:

USE University3;
GO
SELECT ALL * FROM Inventory.StoreItems
WHERE LastName !< N'Alton';
GO

Not Less Than

 
 
 

Comparison For Nullity

 

Checking the Nullity

Depending on how it was created, a field can have an actual value or null. To check  whether a field is holding a null value, use the following formula for the WHERE statement:

WHERE ColumnName IS NULL

In this case, only the records that are NULL on the ColumnName will be considered in the result.

Practical LearningPractical Learning: Checking for Nullity

  1. Click the DepartmentStore1.Inventory.StoreItems tab
  2. In the Criteria pane, delete <= 40
  3. Click the Filter box that corresponds to Manufacturer and type is null
  4. On the main menu, click Query Designer -> Execute SQL

    Checking the Nullity of a Field

  5. Click the SQLQuery1 tab
  6. Change the statement in the Query Editor as follows:
    USE DepartmentStore1;
    GO
    SELECT ItemNumber, Manufacturer, Category, SubCategory, ItemName
    FROM Inventory.StoreItems
    WHERE Category IS NULL;
    GO
  7. Press F5 to execute

Checking the Negative Nullity

Instead of considering the NULL records, the reverse is to get those that are not. To check whether a field doesn't hold a certain value, include the NOT operator in its expression. For example, to check the negativity of an IS NULL expression, use IS NOT NULL to find the records that are not null.

As mentioned already, one of the ways the database engine allows you to check whether an object was already created is by calling the OBJECT_ID() function. In fact, the formula is to find out whether the return value of that function is not null. This can be done as follows:

IF OBJECT_ID('Employees', 'U') IS NOT NULL
  DROP TABLE Employees
GO

This code is asked to enquire about the existence of a table named Employees. If the function returns NULL, it means the table doesn't exist in the current database. If the function returns something else, such as the identifier of the table, then the object exists, and you can take appropriate actions.

Practical LearningPractical Learning: Checking for Not Null

  1. Change the statement as follows:
    SELECT ItemNumber, Manufacturer, Category, SubCategory, ItemName
    FROM Inventory.StoreItems
    WHERE Category IS NOT NULL;
    GO
  2. To execute, press F5
     
    Checking the Negative Nullity of a Field

Conditional Statements

 

Introduction

Conditional Statements

A condition statement is an expression you formulate to evaluate it. Most of the time,  the statement is written so that, when evaluated, it can produce a result of true or false, then, depending on the outcome, you can take action. A condition is usually written as simple as possible to make it clear to you and the SQL interpreter. Although the interpreter never gets confused, if you create a difficult statement, you may receive an unpredictable result.

In the next few sections, we will review the keywords and formulas that Transact-SQL provides to help you formulate clear expressions. Expressions usually start with a keyword, followed by the expression itself. After the expression, you can tell the interpreter what to do. The statement may appear as follows:

Keyword Expression
	Statement

BEGIN...END

With the above formula, we will always let you know what keyword you can use, why, and when. After the expression, you can write the statement in one line. This is the statement that would be executed if/when the Expression of our formula is satisfied. In most cases, you will need more than one line of code to specify the Statement.

As it happens, the interpreter considers whatever comes after the Statement as a unit but only the line immediately after the Expression. To indicate that your Statement covers more than one line, start it with the BEGIN keyword. Then you must use the END keyword to indicate where the Statement ends. In this case, the formula of a conditional statement would appear as follows:

Keyword Expression
BEGIN
	Statement Line 1
	Statement Line 2
	
	Statement Line n
END

You can still use the BEGIN...END combination even if your Statement covers only one line:

Keyword Expression
BEGIN
	Statement
END

Using the BEGIN...END combination makes your code easier to read because it clearly indicates the start and end of the Statement.

IF a Condition is True

Probably the primary comparison you can perform on a statement is to find out whether it is true. This operation is performed using an IF statement in Transact-SQL. Its formula is:

IF Condition
	Statement

When creating an IF statement, first make sure you provide a Condition expression that can be evaluated to produce true or false. To create this Condition, you can use variables and a logical comparison operator. When the interpreter executes the statement, it first examines the Condition to evaluate it to a true result. If the Condition produces true, then the interpreter executes the Statement.

IF...ELSE

The IF condition we used above is appropriate when you only need to know if an expression is true. There is nothing to do in other alternatives.

Here is an example:

-- Square Calculation
DECLARE @Side As Decimal(10,3),
        @Perimeter As Decimal(10,3),
        @Area As Decimal(10,3);
SET     @Side = 48.126;
SET     @Perimeter = @Side * 4;
SET     @Area = @Side * @Side;
IF SIGN(@Side) > 0
    BEGIN
	PRINT N'Square Characteristics';
	PRINT N'-----------------------';
	PRINT N'Side      = N' + TRY_CONVERT(varchar(10), @Side, 10);
	PRINT N'Perimeter = N' + TRY_CONVERT(varchar(10), @Perimeter, 10);
	PRINT N'Area      = N' + TRY_CONVERT(varchar(10), @Area, 10);
    END;
ELSE
	PRINT N'You must provide a positive value';
GO

Here is an example of executing the statement:

Sign

Here is another example:

DECLARE @Number As Decimal(6, 2);
SET @Number = 258.4062;

IF SIGN(@Number) > 0
    PRINT N'The square root of 258.4062 is ' + 
           TRY_CONVERT(varchar(12), SQRT(@Number));
ELSE
    PRINT N'You must provide a positive number';
GO

Here is one example of executing the statement:

Sqrt

CASE...WHEN...THEN

The CASE keyword is used as a conditional operator that considers a value, examines it, and acts on an option depending on the value. The formula of the CASE statement is:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result

	WHEN Value_n THEN Result
END

In the following example, a letter that represents a student is provided. If the letter is m or M, a string is created as Male. If the value is provided as f or F, a string is created as Female:

DECLARE @CharGender Char(1),
	@Gender  Varchar(20);
SET @CharGender = N'F';
SET @Gender = 
	CASE @CharGender
		WHEN N'm' THEN N'Male'
		WHEN N'M' THEN N'Male'
		WHEN N'f' THEN N'Female'
		WHEN N'F' THEN N'Female'
	END;

SELECT N'Student Gender: ' + @Gender;
GO

Practical LearningPractical Learning: Using Cases

  1. Click the SQLQuery1.sql tab
  2. Change the statement in the Query Editor as follows:
    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           CASE aparts.Available
    	    WHEN 0 THEN N'No or Not Yet'
    	    WHEN 1 THEN N'Yes'
           END "Available"
    FROM Presentation.Units aparts
    ORDER BY [Monthly Rent], "Primary Deposit";
    GO
  3. To execute, press F5

Filter

CASE...WHEN...THEN...ELSE

In most cases, you may know the only types of value that would be submitted to a CASE statement. In some other cases, an unpredictable value may be submitted. If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. In this case, the formula of the CASE statement would be:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result
	WHEN Value_n THEN Result
	
	ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. If you don't produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL. Here is an example:

DECLARE @CharGender Char(1),
	@Gender  NVarchar(20);
SET     @CharGender = N'Q';
SET @Gender = 
	CASE @CharGender
		WHEN N'm' THEN N'Male'
		WHEN N'M' THEN N'Male'
		WHEN N'f' THEN N'Female'
		WHEN N'F' THEN N'Female'
		ELSE N'Unknown'
	END;

SELECT N'Student Gender: ' + @Gender;
GO

CASE...WHEN...THEN...ELSE

This means that it is a valuable safeguard to always include an ELSE sub-statement in a CASE statement.

WHILE

To examine a condition and evaluate it before taking action, you can use the WHILE operator. The basic formula of this statement is:

WHILE Expression 
    Statement

When implementing this statement, first provide an Expression after the WHILE keyword. The Expression must produce a true or a false result. If the Expression is true, then the interpreter executes the Statement. After executing the Statement, the Expression is checked again. AS LONG AS the Expression is true, it will keep executing the Statement. When or once the Expression becomes false, it stops executing the Statement. This scenario can be illustrated as follows:

WHILE

Here is an example:

DECLARE @Number As int

WHILE @Number < 5
	SELECT @Number AS Number
GO

To effectively execute a while condition, you should make sure you provide a mechanism for the interpreter to get a referenced value for the condition, variable, or expression being checked. This is sometimes in the form of a variable being initialized although it could be some other expression. Such a while condition could be illustrated as follows:

WHILE

Here is an example:

DECLARE @Number As int
SET @Number = 1
WHILE @Number < 5
    BEGIN
	SELECT @Number AS Number
	SET @Number = @Number + 1
    END
GO

 This would produce:

WHILE

 

Logic-Based Functions

 

Choosing Among Values

Consider the following table:

CREATE TABLE Administration.Contractors
(
    ContractorCode nchar(7),
    FirstName nvarchar(25),
    LastName nvarchar(25),
    Gender int,
    EmploymentStatus nvarchar(26),
    TimeRecordingCode nchar(4)
);
GO

To reduce the likelihood of mistakes, we would like to make the user choose a value for the gender, a value for the employment status, and a value for the time recording code. Of course there are various ways this can be done. Nevertheless, Transact-SQL provides a function named CHOOSE.

The CHOOSE() function is used to consider an array of values and get the value at a specified index. The syntax of this function is:

CHOOSE(index int,
       val_1 sql_variant,
       val_2 sql_variant [, val_n sql_variant])
       RETURNS sql_variant;

The first argument is an integral value. The second to last arguments represent a series of values. The value can be of any Transact-SQL type (integer, decimal number, string, etc). The values are separated by commas so that each argument or value occupies a specific position, which is its index. When called, this function produces the item located at the position corresponding to the index argument. Here is an example:

INSERT INTO Administration.Contractors
VALUES(N'931-846', N'Bertrand', N'Onobiono',
       CHOOSE(1, 1, 2, 3), CHOOSE(1, N'Full-Time', N'Part-Time', N'Temporary'),
       CHOOSE(2, N'FLEM', N'PRTL', N'CNTR', N'CVLN')),
      (N'527-168', N'chris', N'Pastore',
       CHOOSE(3, 1, 2, 3), CHOOSE(2, N'Full-Time', N'Part-Time', N'Temporary'),
       CHOOSE(3, N'FLEM', N'PRTL', N'CNTR', N'CVLN')),
      (N'824-506', N'Joan', N'Orendon',
       CHOOSE(2, 1, 2, 3), CHOOSE(2, N'Full-Time', N'Part-Time', N'Temporary'),
       CHOOSE(1, N'FLEM', N'PRTL', N'CNTR', N'CVLN')),
      (N'527-168', N'chris', N'Pastore',
       CHOOSE(3, 1, 2, 3), CHOOSE(2, N'Full-Time', N'Part-Time', N'Temporary'),
       CHOOSE(3, N'FLEM', N'PRTL', N'CNTR', N'CVLN')),
      (N'248-007', N'James', N'Orendon',
       CHOOSE(1, 1, 2, 3), CHOOSE(1, N'Full-Time', N'Part-Time', N'Temporary'),
       CHOOSE(4, N'FLEM', N'PRTL', N'CNTR', N'CVLN'));
GO

The records can be verified with the following:

SELECT staff.ContractorCode [Empl Code],
	   staff.FirstName [First Name],
	   staff.LastName [Last Name],
	   staff.Gender,
	   staff.EmploymentStatus [Emplmt Status],
	   staff.TimeRecordingCode [Time Code]
FROM Administration.Contractors staff;
GO

Immediate If

Consider the following table of houses on rent:

USE master
GO
CREATE DATABASE ApartmentsRentings1;
GO

USE ApartmentsRentings1;
GO

CREATE SCHEMA Listing;
GO
CREATE TABLE Listing.Properties
(
	PropertyNumber  nvarchar(10),
	PropertyType    nvarchar(20),
	Bedrooms    	smallint,
	Bathrooms   	float,
	MonthlyRate     money,
	SecurityDeposit	money
);
GO

INSERT INTO Listing.Properties(PropertyNumber, PropertyType,
			Bedrooms, Bathrooms, MonthlyRate, SecurityDeposit)
VALUES(N'7283-0884', N'Condominium',   2,   2, 1475, 1000),
      (N'8241-0600', N'Apartment',     1,   1,  895,  250),
      (N'5927-5940', N'Apartment',     0,   1,  865,  225),
      (N'8250-2082', N'Townhouse',     3, 2.5, 1450,  850),
      (N'2729-3971', N'Apartment',     1,   1,  925,  275),
      (N'2864-4816', N'Townhouse',     3, 2.5, 1850, 1200),
      (N'6200-8048', N'Apartment',     2,   1, 1100,  500),
      (N'6286-6408', N'Apartment',     0,   1,  950,  450),
      (N'9736-6181', N'Single Family', 3, 2.5, 1650, 1200),
      (N'6304-0796', N'Apartment',     2,   1, 1500, 1000),
      (N'6029-7570', N'Condominium',   1,   1,  820,  500),
      (N'9255-7497', N'Condominium',   2,   2, 1475, 1000),
      (N'2930-8048', N'Condominium',   1,   1, 1265,  850),
      (N'6209-2252', N'Condominium',   1,   1, 1265,  850),
      (N'3730-1637', N'Single Family', 4, 2.5, 2650, 1500),
      (N'2927-1539', N'Apartment',     0,   1,  825,  200),
      (N'4855-5702', N'Townhouse',     3, 2.5, 1580, 1200),
      (N'9359-9502', N'Condominium',   1,   1,  760,  500),
      (N'3840-0002', N'Apartment',     3,   2, 1450,  600),
      (N'2057-0794', N'Single Family', 4, 3.5, 2880, 1500),
      (N'4927-5396', N'Apartment',     3,   2, 1350,  425),
      (N'3006-6088', N'Apartment',     1,   1, 1100,  600),
      (N'1397-0492', N'Apartment',     2,   1, 1050,  350);
GO

Transact-SQL provides the IF keyword that can be used to check a condition. If the condition is true, you can apply a certain expression. In case the condition is false, there is the ELSE keyword that can be used to consider the alternative. Instead of writing such an IF ... ELSE expression, Transact-SQL provides a function named IIF. Its syntax is:

IIF(Condition to Check, What to do if Condition is true, What to do if Condition is false)
RETURNS BIT

The IIF() function takes three arguments. The first parameter is logical condition that must be checked and would produce a true or a false result. If that result is true, the second parameter would apply. If the condition produces a false result, the third parameter would apply. Here is an example:

USE ApartmentsRentings1;
GO

SELECT props.PropertyNumber,
       props.PropertyType + N', ' + TRY_CONVERT(nvarchar(20), props.Bedrooms) +
           IIF(TRY_CONVERT(nvarchar(20), props.Bedrooms) < 2, N' bedroom', N' bedrooms') "Property Details"
FROM Listing.Properties props;
GO

This would produce:

Immediate If

Of course, you can call as many IIF() functions as you want. Here is an example that uses two:

USE ApartmentsRentings1;
GO

SELECT props.PropertyNumber,
       props.PropertyType + N', ' + TRY_CONVERT(nvarchar(20), props.Bedrooms) +
           IIF(TRY_CONVERT(nvarchar(20), props.Bedrooms) < 2, N' bedroom', N' bedrooms')
		+ N', ' +	TRY_CONVERT(nvarchar(20), props.Bathrooms) + 
	   IIF(TRY_CONVERT(nvarchar(20), props.Bedrooms) < 2, N' bathroom', N' bathrooms') "Property Details"
FROM Listing.Properties props;
GO

As one IF condition or one IF ... ELSE condition can be nested in another, we also know that one function can be called in the body or in the parentheses of another, the IIF() function also allows you to nest one inside another. The formula would be:

IIF(Main Condition to Check,
    What to do if Main Condition is true,
    	IIF(Nested Condition to Check,
    	    What to do if Nested Condition is true,
    	    What to do if Nested Condition is false)
	RETURNS BIT)
RETURNS BIT

Here is an example of an IIF() function nested into another another:

USE ApartmentsRentings1;
GO

SELECT props.PropertyNumber,
       IIF(TRY_CONVERT(nvarchar(20), props.Bedrooms) = 0, N'Efficiency', props.PropertyType + N', ' + 
           IIF(TRY_CONVERT(nvarchar(20), props.Bedrooms) < 2,
		N' bedroom', N' bedrooms')) "Property Details"
FROM Listing.Properties props;
GO

This would produce:

Immediate If

In the same way, you can nest IIF() functions inside of others. A formula to nested one IIF() call in another and the same IIF() in another is:

IIF(Main Condition to Check,
    What to do if Main Condition is true,
    	IIF(Nested Condition to Check,
    	    What to do if Nested Condition is true,
        	IIF(Nested Condition to Check,
                    What to do if Nested Condition is true,
    		    What to do if Nested Condition is false)
    	        ) RETURNS BIT
	) RETURNS BIT)
RETURNS BIT

 

Practical LearningPractical Learning: Ending the Lesson

  1. Close Microsoft SQL Server
  2. If asked whether you want to save the file, click No
 
 
   
 

Previous Copyright © 2008-2013 FunctionX Next