Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

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
Mimer SQL User's Manual TOC PREV NEXT INDEX