SQL Series...19

Specifying Values

You can represent values in several ways. You can represent them directly, or you can derive them with functions or expressions. This article describes the various kinds of values, as well as functions and expressions.

Functions examine data and calculate a value based on the data. Expressions are combinations of data items that SQL evaluates to produce a single value.

Values

SQL recognizes several kinds of values:

Row values Literal values Variables

Special variables Column references

Row values

The most visible values in a database are table row values. These are the values that each row of a database table contains. A row value is typically made up of multiple components, because each column in a row contains a value. A field is the intersection of a single column with a single row. A field contains a scalar, or atomic, value. A value that’s scalar or atomic has only a single component.

Literal values

In SQL, either a variable or a constant may represent a value. Logically enough, the value of a variable may change from time to time, but the value of a constant never changes. An important kind of constant is the literal value. You may consider a literal to be a WYSIWYG value, because What You See Is What You Get. The representation is itself the value.

Just as SQL has many data types, it also has many types of literals. Table 7-1 shows some examples of literals of the various data types.

Notice that single quotes enclose the literals of the nonnumeric types. These marks help to prevent confusion; they can, however, also cause problems, as you can see in Table 

Table	Example Literals of Various Data Types
Data Type		Example Literal
BIGINT		8589934592
INTEGER		186282
SMALLINT		186
NUMERIC		186282.42
DECIMAL		186282.42
REAL		6.02257E23
DOUBLE PRECISION		3.1415926535897E00
FLOAT		6.02257E23
CHARACTER(15)		'GREECE		'
Note: Fifteen total characters and spaces are between the quote marks above.
VARCHAR (CHARACTER VARYING)		'lepton'
NATIONAL CHARACTER(15)		'Ε←m←mΑσ			' 1
Note: Fifteen total characters and spaces are between the quote marks above.
NATIONAL CHARACTER VARYING		'λεπτον' 2
CHARACTER LARGE OBJECT (CLOB)		(A really long character string)
BINARY LARGE OBJECT (BLOB)		(A really long string of ones and zeros)
DATE		DATE '1969-07-20'
TIME(2)		TIME '13.41.32.50'
TIMESTAMP(0)		TIMESTAMP '1998-05-17-13.03.16.000000'
TIME WITH TIMEZONE(4)		TIME '13.41.32.5000-08.00'
TIMESTAMP WITH TIMEZONE(0)		TIMESTAMP
		'1998-05-17-13.03.16.0000+02.00'
INTERVAL DAY		INTERVAL '7' DAY

What if a literal is a character string that itself contains a single quote? In that case, you must type two single quotes to show that one of the quote marks that you’re typing is a part of the character string and not an indicator of the end of the string. You’d type ‘Earth’’s atmosphere’, for example, to repre-sent the character literal ‘Earth’s atmosphere’.

Variables

The ability to manipulate literals and other kinds of constants while dealing with a database is great, but it is helpful to have variables, too. In many cases, you’d need to do much more work if you didn’t have variables. A variable, by the way, is a quantity that has a value that can change. Look at the following example to see why variables are valuable.

Suppose that you’re a retailer who has several classes of customers. You give your high-volume customers the best price, your medium-volume customers the next best price, and your low-volume customers the highest price. You want to index all prices to your cost of goods. For your F-117A product, you decide to charge your high-volume customers (Class C) 1.4 times your cost of goods. You charge your medium-volume customers (Class B) 1.5 times your cost of goods, and you charge your low-volume customers (Class A) 1.6 times your cost of goods.

You store the cost of goods and the prices that you charge in a table named

PRICING. To implement your new pricing structure, you issue the following

SQL commands:

UPDATE PRICING

SET Price = Cost * 1.4

WHERE Product = ‘F-117A’

AND Class = ‘C’ ;

UPDATE PRICING

SET Price = Cost * 1.5

WHERE Product = ‘F-117A’

AND Class = ‘B’ ;

UPDATE PRICING

SET Price = Cost * 1.6

WHERE Product = ‘F-117A’

AND Class = ‘A’ ;

This code is fine and meets your needs — for now. But what if aggressive competition begins to eat into your market share? You may need to reduce your margins to remain competitive. You need to enter something along the lines of the following commands:

UPDATE PRICING

SET Price = Cost * 1.25

WHERE Product = ‘F-117A’

AND Class = ‘C’ ;

UPDATE PRICING

SET Price = Cost * 1.35

WHERE Product = ‘F-117A’

AND Class = ‘B’ ;

UPDATE PRICING

SET Price = Cost * 1.45

WHERE Product = ‘F-117A’

AND Class = ‘A’ ;

If you’re in a volatile market, you may need to rewrite your SQL code repeat-edly. This task can become tedious, particularly if prices appear in multiple places in your code. You can minimize this problem if you replace literals (such as 1.45) with variables (such as :multiplierA). Then you can per-form your updates as follows:

UPDATE PRICING

SET Price = Cost * :multiplierC

WHERE Product = ‘F-117A’

AND Class = ‘C’ ;

UPDATE PRICING

SET Price = Cost * :multiplierB

WHERE Product = ‘F-117A’

AND Class = ‘B’ ;

UPDATE PRICING

SET Price = Cost * :multiplierA

WHERE Product = ‘F-117A’

AND Class = ‘A’ ;

Now whenever market conditions force you to change your pricing, you need to change only the values of the variables :multiplierC, :multiplierB, and :multiplierA. These variables are parameters that pass to the SQL code, which then uses the variables to compute new prices.

Sometimes, you see variables that you use in this way called parameters and, at other times, host variables. Variables are called parameters if they are in applications written in SQL module language and host variables if they’re used in embedded SQL.

Embedded SQL means that SQL statements are embedded into the code of an application written in a host language. Alternatively, you can use SQL module language to create an entire module of SQL code. The host language applica-tion then calls the module. Either method can give you the capabilities that you want. The approach that you use depends on your SQL implementation.

Special variables

If a user on a client machine connects to a database on a server, this connec-tion establishes a session. If the user connects to several databases, the ses-sion associated with the most recent connection is considered the current session; previous sessions are considered dormant. SQL:2003 defines several special variables that are valuable on multiuser systems. These variables keep track of the different users. The special variable SESSION_USER, for example, holds a value that’s equal to the user authorization identifier of the current SQL session. If you write a program that performs a monitoring func-tion, you can interrogate SESSION_USER to find out who is executing SQL statements.

An SQL module may have a user-specified authorization identifier associated with it. The CURRENT_USER variable stores this value. If a module has no such identifier, CURRENT_USER has the same value as SESSION_USER.

The SYSTEM_USER variable contains the operating system’s user identifier.

This identifier may differ from that user’s identifier in an SQL module. A

user may log onto the system as LARRY, for example, but identify himself to a

module as PLANT_MGR. The value in SESSION_USER is PLANT_MGR. If he makes

no explicit specification of the module identifier, and CURRENT_USER also con-

tains PLANT_MGR, SYSTEM_USER holds the value LARRY.

One use of the SYSTEM_USER, SESSION_USER, and CURRENT_USER special variables is to track who is using the system. You can maintain a log table and periodically insert into that table the values that SYSTEM_USER, SESSION_USER, and CURRENT_USER contain. The following example shows how:

INSERT INTO USAGELOG (SNAPSHOT)

VALUES (‘User ‘ || SYSTEM_USER ||

‘ with ID ‘ || SESSION_USER ||

‘ active at ‘ || CURRENT_TIMESTAMP) ;

This statement produces log entries similar to the following example:

User LARRY with ID PLANT_MGR active at 2003-03-07-23.50.00

Column references

Columns contain values, one in each row of a table. SQL statements often refer to such values. A fully qualified column reference consists of the table name, a period, and then the column name (for example, PRICING.Product). Consider the following statement:

SELECT PRICING.Cost

FROM PRICING

WHERE PRICING.Product = ‘F-117A’ ;

PRICING.Product is a column reference. This reference contains the value ‘F-117A’. PRICING.Cost is also a column reference, but you don’t know its value until the preceding SELECT statement executes.

Because it only makes sense to reference columns in the current table, you don’t generally need to use fully qualified column references. The following statement, for example, is equivalent to the previous one:

SELECT Cost

FROM PRICING

WHERE Product = ‘F-117A’ ;

Sometimes, you may be dealing with more than one table. Two tables in a database may contain one or more columns with the same name. If so, you must fully qualify column references for those columns to guarantee that you get the column you want.

For example, suppose that your company maintains facilities at Kingston and at Jefferson, and you maintain separate employee records for each site. You name the employee table at Kingston EMP_KINGSTON, and you name the Jefferson employee table EMP_JEFFERSON. You want a list of employees who work at both sites, so you need to find the employees whose names appear in both tables. The following SELECT statement gives you what you want:

SELECT EMP_KINGSTON.FirstName, EMP_KINGSTON.LastName

FROM EMP_KINGSTON, EMP_JEFFERSON

WHERE EMP_KINGSTON.EmpID = EMP_JEFFERSON.EmpID ;

Because the employee’s ID number is unique and is the same regardless of work site, you can use this ID as a link between the two tables. This retrieval returns only the names of employees who appear in both tables.