www.mimer.com/developer

Selecting Specific Rows

---

Rows are selected in the SELECT statement according to the search condition in the WHERE clause. This condition relates column value(s) to expressions.

Comparison Conditions and WHERE

Comparison operators that may be used in the WHERE clause are:

Operator	Explanation
=	equal to
<>	not equal to
<	less than
<=	less than or equal to
>	greater than
>=	greater than or equal to

Comparisons can be combined in the search condition using the logical operators AND and OR, and reversed using NOT.

Each comparison must be expressed in full; for example

 WHERE PRICE > 800 AND PRICE < 1000
 

may not be expressed as

 WHERE PRICE > 800 AND < 1000

Comparing Character Strings

Character strings are compared character by character from left to right.

If strings are of different lengths, the shorter is conceptually padded to the right with blanks before the comparison is made (i.e. character difference takes precedence over length difference).

The collating sequence for characters is an extended ASCII character set as defined by ISO 8859-1, see the Mimer SQL Reference Manual for the exact sequence.

To retrieve the room type, price, and date from which the prices apply for all rooms with hotel code LAP and a cost of under 700:

 SELECT  ROOMTYPE, PRICE, FROM_DATE, TO_DATE
 FROM    ROOM_PRICES
 WHERE   HOTELCODE = 'LAP' AND PRICE < 700;

Result:

ROOMTYPE	PRICE	FROM_DATE	TO_DATE
NSSGLB	660	1997-11-15	1998-03-10
NSSGLS	680	1997-08-08	1997-11-14
NSSGLS	640	1997-11-15	1998-03-10
SSGLB	660	1997-11-15	1998-03-10
SSGLS	680	1997-08-08	1997-11-14
SSGLS	640	1997-11-15	1998-03-10

Comparing Temporal Data

When stating conditions on temporal data in tables, datetime and interval literals can be specified. There are also the pseudo literals CURRENT_DATE, LOCALTIME and LOCALTIMESTAMP which read the server clock and return that value.

If there is more than one occurrence of these pseudo literals in a statement the clock is only read once.

Retrieve guests who requested a wake up call at 6 o'clock today.

 SELECT ROOMNO 
 FROM WAKE_UP 
 WHERE WAKE_DATE = CURRENT_DATE
 AND WAKE_TIME = TIME '06:00:00';

Result:

ROOMNO

LAP112

SKY111

STG009

Are there any guests scheduled for check in today?

 SELECT  RESERVED_FNAME, RESERVED_LNAME
 FROM    BOOK_GUEST
 WHERE   ARRIVE = CURRENT_DATE;

Result:

RESERVED_FNAME	RESERVED_LNAME
ALEX	OLSSON
BERTIL	GUSTAVSSON
URBAN	FRANSSON

For an example of interval literals, see Datetime Arithmetic and Functions.

Pattern Conditions

LIKE is used to search for character strings that match a specified pattern.

Patterns in the LIKE condition can be written with 'wildcard' characters (also called 'meta-characters'):

Pattern	Explanation
_	(underscore) stands for any single character
%	stands for any sequence of zero or more characters

Wildcards only have significance in LIKE predicates.

Find all guests at the Hotel Laponia whose names include 'HANSEN':

 SELECT  GUEST_LNAME
 FROM    BOOK_GUEST
 WHERE   GUEST_LNAME LIKE '%HANSEN%' AND HOTELCODE = 'LAP';

Result:

GUEST_LNAME

JOHANSEN

HANSEN

Find all guests at the Hotel Laponia whose last names do not include 'HANSEN'.

 SELECT  GUEST
 FROM    BOOK_GUEST
 WHERE   GUEST_LNAME NOT LIKE '%HANSEN%' AND HOTELCODE = 'LAP';

Result:

GUEST_LNAME

DATE

ALVE

KRISTOFERSEN

HOLMER

KULLMER

SMITH

SCHMIDT

ZETTERBERG

HANSSON

Remember that character strings in Mimer SQL statements are always written within apostrophes (').

A LIKE predicate where the pattern string does not contain any wildcard characters is essentially equivalent to a basic predicate using the '=' operator, except that comparison strings in an '=' comparison are conceptually padded with blanks whereas those in the LIKE comparison are not.

For example:

 'SKYLINE  ' =    'SKYLINE'           is true
 'SKYLINE  ' LIKE 'SKYLINE  '         is true
 'SKYLINE  ' LIKE 'SKYLINE%'          is true

but

 'SKYLINE  ' LIKE 'SKYLINE'           is false
 

The LIKE predicate may include an ESCAPE clause defining a character which is used to 'escape' wildcard characters. A wildcard character immediately following an escape character is taken at face value. See the Mimer SQL Reference Manual for more details.

More about Searching for Character Strings

Some other examples of searching for character strings are:

LIKE '%P%'	matches any string that contains an upper-case 'P'.
LIKE '_abc'	matches any four letter character string ending with lower case 'abc'.
LIKE '%A\%' ESCAPE '\'	matches any string ending with 'A%'.
LIKE 'D_d_'	matches any four letter string with D and d in the first and third positions respectively. Examples of possible values: Dude, Dads.

Set Conditions

IN and Not IN

The operator IN finds the values that are contained in a set of values. The set is given as a comma-separated list enclosed in parentheses.

NOT IN finds values which are not contained in the specified set.

Which hotels are in Stockholm or Copenhagen?

 SELECT  NAME, CITY
 FROM    HOTEL
 WHERE   CITY IN ('STOCKHOLM','COPENHAGEN');

Result:

NAME	CITY
LAPONIA	STOCKHOLM
ST. GEORGE	STOCKHOLM
WINSTON	COPENHAGEN

Which hotels are not in Stockholm or Copenhagen?

 SELECT  NAME, CITY
 FROM    HOTEL
 WHERE   CITY NOT IN ('STOCKHOLM','COPENHAGEN');

Result:

NAME	CITY
SKYLINE	UPPSALA
Winston	London
WINSTON	GOTHENBURG

BETWEEN and NOT BETWEEN

The operators BETWEEN and NOT BETWEEN are used to find values that are within or outside an interval. The interval includes the limits specified in the BETWEEN condition.

Find which room types that have prices in the range 700 to 1000 at hotel LAPONIA:

 SELECT  ROOMTYPE, PRICE
 FROM    ROOM_PRICES
 WHERE   PRICE BETWEEN 700 AND 1000
 AND     HOTELCODE = 'LAP'

Result:

ROOMTYPE	PRICE
NSDBLB	900
NSDBLB	830
NSDBLS	760
NSDBLS	710
NSDBLS	800
SDBLB	900
SDBLB	830
SDBLS	710
SDBLS	760
SSGLB	800

Find the date, charge code and amount for items billed on dates outside the range 1997-08-30 and 1997-09-01 for the reservation number 1371:

 SELECT  ON_DATE, CHARGE_CODE, COST
 FROM    BILL
 WHERE   RESERVATION = 1371
 AND     ON_DATE NOT BETWEEN TIMESTAMP '1997-08-30 00:00:00' AND
                             TIMESTAMP '1997-09-01 23:59:59';

Result:

ON_DATE	CHARGE_CODE	COST
1997-07-06 13:38:19	700	-
1997-07-06 13:38:19	230	200
1997-07-07 13:38:19	100	100
1997-07-08 13:38:19	100	100
1997-07-08 13:38:19	200	-
1997-07-08 13:38:20	230	200
1997-07-09 13:38:20	100	100
1997-07-09 13:38:20	270	95
1997-07-10 13:38:20	100	100
1997-07-10 13:38:20	330	120
1997-07-11 13:38:20	100	100
1997-07-11 13:38:20	200	-
1997-07-12 13:38:20	100	100

BETWEEN may also be used for character comparisons. Strings are compared character by character from left to right.

For example:

 SELECT  NAME
 FROM    HOTEL
 WHERE   NAME BETWEEN 'SKYLINE' AND 'WINSTON';

Result:

NAME

SKYLINE

ST. GEORGE

WINSTON

WINSTON

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se