vb.net qr code sample CASE Expressions in VB.NET

Create Data Matrix in VB.NET CASE Expressions

CASE Expressions
Encode Data Matrix 2d Barcode In Visual Basic .NET
Using Barcode creation for VS .NET Control to generate, create DataMatrix image in .NET framework applications.
www.OnBarcode.com
Data Matrix Reader In VB.NET
Using Barcode scanner for .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
The CASE expression allows an alternative value to be displayed depending on the value of a column. For example, a CASE expression can provide Texas in a result set for rows that have the value TX in the state column. Let s take a look at the syntax of the CASE expression. It has two different forms: the simple CASE and the searched CASE.
Generating Code 39 Full ASCII In VB.NET
Using Barcode drawer for .NET framework Control to generate, create Code 3 of 9 image in VS .NET applications.
www.OnBarcode.com
EAN / UCC - 13 Creator In Visual Basic .NET
Using Barcode maker for Visual Studio .NET Control to generate, create European Article Number 13 image in .NET framework applications.
www.OnBarcode.com
Simple CASE Expressions
Generate UPC-A In VB.NET
Using Barcode generator for Visual Studio .NET Control to generate, create UCC - 12 image in .NET framework applications.
www.OnBarcode.com
PDF 417 Generator In VB.NET
Using Barcode maker for .NET framework Control to generate, create PDF-417 2d barcode image in Visual Studio .NET applications.
www.OnBarcode.com
This is the simple CASE syntax, where the ELSE part is optional:
QR Code 2d Barcode Generation In Visual Basic .NET
Using Barcode maker for .NET framework Control to generate, create QR Code image in VS .NET applications.
www.OnBarcode.com
Encode Leitcode In Visual Basic .NET
Using Barcode encoder for VS .NET Control to generate, create Leitcode image in .NET applications.
www.OnBarcode.com
CASE <case operand> WHEN <when operand> THEN <when result> ELSE <else result> END
Data Matrix ECC200 Drawer In VB.NET
Using Barcode drawer for VS .NET Control to generate, create Data Matrix image in .NET framework applications.
www.OnBarcode.com
Data Matrix Encoder In VS .NET
Using Barcode drawer for Reporting Service Control to generate, create Data Matrix 2d barcode image in Reporting Service applications.
www.OnBarcode.com
The CASE keyword is followed by a column name or expression that s to be tested against the operand (a scalar value) following the WHEN keyword. If <case operand> has the same value as <when operand>, <when result> is used; otherwise, <else result> is used as the selection list value.
Creating Barcode In Visual Studio .NET
Using Barcode printer for Visual Studio .NET Control to generate, create Barcode image in .NET framework applications.
www.OnBarcode.com
Barcode Creator In Java
Using Barcode creator for Eclipse BIRT Control to generate, create Barcode image in BIRT applications.
www.OnBarcode.com
Try It Out: Using a Simple CASE Expression
Code 39 Printer In Java
Using Barcode maker for Java Control to generate, create Code-39 image in Java applications.
www.OnBarcode.com
Print ANSI/AIM Code 39 In None
Using Barcode maker for Office Excel Control to generate, create ANSI/AIM Code 39 image in Excel applications.
www.OnBarcode.com
Let s use a simple CASE expression.
Making Code 128 Code Set B In None
Using Barcode encoder for Font Control to generate, create USS Code 128 image in Font applications.
www.OnBarcode.com
USS Code 39 Maker In Java
Using Barcode printer for BIRT Control to generate, create USS Code 39 image in BIRT reports applications.
www.OnBarcode.com
CHAPTER 11 s LEARNING MORE ABOUT QUERIES
Drawing PDF 417 In None
Using Barcode creation for Font Control to generate, create PDF-417 2d barcode image in Font applications.
www.OnBarcode.com
QR Code ISO/IEC18004 Creation In Objective-C
Using Barcode drawer for iPad Control to generate, create QR Code JIS X 0510 image in iPad applications.
www.OnBarcode.com
Enter the following query into SSMSE and execute it. You should see the results in Figure 11-9.
PDF-417 2d Barcode Generator In None
Using Barcode encoder for Online Control to generate, create PDF417 image in Online applications.
www.OnBarcode.com
Recognize Data Matrix 2d Barcode In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
select distinct year(orderdate) NumYear, case year(orderdate) when 1998 then 'Last year' else 'Prior year' end LabYear from orders
Figure 11-9. Using a simple CASE expression
How It Works
You simply labeled years as either Last year or Prior year depending on whether they were 1998 (the last year for orders in this version of the Northwind database) or earlier (in this database none are later than 1998). The first two lines got a list of the distinct years (in the Orders table):
select distinct year(orderdate) NumYear,
CHAPTER 11 s LEARNING MORE ABOUT QUERIES
Note that you specified an alias NumYear, but because it doesn t include blanks, you don t have to enclose it in single quotes (or brackets). The next item in the select list (note that a CASE expression is used just like a column name or function call) is a simple CASE expression where you provided the result of the YEAR function applied to the order date as the <case operand>, the numeric literal 1998 as the <when operand>, and two strings to label the last year and the prior years, depending on whether the year is 1998 (in other words, whether it matches the <when operand>):
case year(orderdate) when 1998 then 'Last year' else 'Prior year' end LabYear
Note that because a CASE expression is merely another member of a select list, you can (and do) give it an alias, LabYear.
Try It Out: Using a More Complex CASE Expression
Let s modify this CASE expression to get an idea of how flexible it can be. Enter the following query into SSMSE, and execute it. You should see the results in Figure 11-10.
select distinct year(orderdate) NumYear, case year(orderdate) when 1998 then str(year(orderdate)) else case year(orderdate) when 1997 then 'Prior' else 'Earlier' end end LabYear from orders
CHAPTER 11 s LEARNING MORE ABOUT QUERIES
Figure 11-10. Using a more complex simple CASE expression
How It Works
You did a couple interesting things here. First, you changed the label for the last year to the year itself rather than a string, showing that the <when operand> can be a data value (here, in fact, the result of a function applied to a column):
when 1998 then str(year(orderdate))
Note that you used the STR function to convert the integer returned by YEAR to a string, because you re planning to use strings for the alternative labels, and a CASE expression must return values of a single data type. You then nested a CASE expression inside the original ELSE (they can also be nested in the WHEN part) to support labeling the other years separately. You labeled 1997 as Prior and all others as Earlier:
else case year(orderdate) when 1997 then 'Prior'
CHAPTER 11 s LEARNING MORE ABOUT QUERIES
else 'Earlier' end
Many other variations are possible. The simple CASE expression can be quite complex. Exploit it to achieve query results that would otherwise require a lot more work for both you and the database! Now, let s examine the searched CASE.
Searched CASE Expressions
The following is the searched CASE syntax, where the ELSE part is optional:
CASE WHEN <search condition> THEN <when result> ELSE <else result> END
Note the differences between the searched and simple CASEs. The searched CASE has no <case operand>, and the <when operand> is replaced by a <search condition>. These seemingly minor changes add an enormous amount of power.
Copyright © OnBarcode.com . All rights reserved.