Computed values in the select list

You can perform computations with data from numeric columns or on numeric constants in a select list.

Bitwise operators

The bitwise operators are a Transact-SQL extension that you can use with integers. These operators convert each integer operand into its binary representation, then evaluates the operands column by column. A value of 1 corresponds to true; a value of 0 corresponds to false.

Table 2-1 shows the bitwise operators.

For more information on bitwise operators, see the Reference Manual.

Arithmetic operators

Table 2-2 shows the available arithmetic operators.

With the exception of the modulo operator, you can use any arithmetic operator on any numeric column (bigint, int, smallint, tinyint, unsigned bigint, unsigned int, unsigned smallint, numeric, decimal, float, or money).

A modulo operator, which can be used on all integer type columns, finds the remainder after a division involving two numbers. For example, using integers: 21 % 11 = 10 because 21 divided by 11 equals 1, with a remainder of 10. You can obtain a non-interger result with numeric or decimal datatypes: 1.2 % 0.07 = 0.01 because 1.2 / 0.07 = 17 * 0.07 + 0.01. You receive similar results from float and real datatype calculations: 1.2e0 % 0.07 = 0.010000.

You can perform certain arithmetic operations on date/time columns using the date functions. See Chapter 15, “Using the Built-In Functions in Queries,” for information. You can use all of these operators in the select list with column names and numeric constants in any combination. For example, to see what a projected sales increase of 100 percent for all the books in the titles table looks like, enter:

select title_id, total_sales, total_sales * 2 
from titles 

Here are the results:

title_id     total_sales 
--------     -----------       ---------
BU1032              4095            8190 
BU1111              3876            7752 
BU2075             18722           37444 
BU7832              4095            8190 
MC2222              2032            4064 
MC3021             22246           44492 
MC3026              NULL            NULL 
PC1035              8780           17560 
PC8888              4095            8190 
PC9999              NULL            NULL 
PS1372               375             750 
PS2091              2045            4090 
PS2106               111             222 
PS3333              4072            8144 
PS7777              3336            6672 
TC3218               375             750 
TC4203             15096           30192 
TC7777              4095            8190 
 
(18 rows affected) 

Notice the null values in the total_sales column and the computed column. Null values have no explicitly assigned values. When you perform any arithmetic operation on a null value, the result is NULL. You can give the computed column a heading, “proj_sales” for example, by entering:

select title_id, total_sales, 
     proj_sales = total_sales * 2 
from titles 

title_id     total_sales     proj_sales  
---------    -----------     ----------- 
BU1032             4095            8190
 ....

Try adding character strings such as “Current sales =” and “Projected sales are” to the select statement. The column from which the computed column is generated does not have to appear in the select list. The total_sales column, for example, is shown in these sample queries only for comparison of its values with the values from the total_sales * 2 column. To see only the computed values, enter:

select title_id, total_sales * 2 
from titles 

Arithmetic operators also work directly with the data values in specified columns, when no constants are involved. For example:

select title_id, total_sales * price 
from titles 

title_id 
--------     ---------- 
BU1032        81,859.05 
BU1111        46,318.20 
BU2075        55,978.78 
BU7832        81,859.05 
MC2222        40,619.68 
MC3021        66,515.54 
MC3026             NULL 
PC1035       201,501.00 
PC8888        81,900.00 
PC9999             NULL 
PS1372         8,096.25 
PS2091        22,392.75 
PS2106           777.00 
PS3333        81,399.28 
PS7777        26,654.64 
TC3218         7,856.25 
TC4203       180,397.20 
TC7777        61,384.05 
 
(18 rows affected) 

Computed columns can also come from more than one table. The joining and subqueries chapters in this manual include information on multitable queries.

As an example of a join, this query multiplies the number of copies of a psychology book sold by an outlet (the qty column from the salesdetail table) by the price of the book (the price column from the titles table).

select salesdetail.title_id, stor_id, qty * price 
from titles, salesdetail 
where titles.title_id = salesdetail.title_id 
and titles.title_id = "PS2106"

title_id         stor_id
---------------- -----------  ------
PS2106           8042         210.00  
PS2106           8042         350.00  
PS2106           8042         217.00  
 
(3 rows affected) 

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF