Chapter 5 SQL predicate BETWEEN

Chapter 5 SQL predicate BETWEEN

outline

scalar-expression BETWEEN lowval AND highval

parameter

  • Scalar expression - a scalar expression (most commonly a data column) that compares its value to the range of values between low and high values, including high values.
  • lowval - an expression that resolves to a low collation sequence value, specifying the beginning of a value range that matches each value in a scalar expression.
  • highval - an expression that resolves to a high collation sequence value, specifying the end of the value range to match each value in the scalar expression.

describe

The BETWEEN predicate allows you to select data values within the specified range of lowval and highval.
This range includes low values and high values themselves.
This is equivalent to a pair of greater than or equal operators and a pair of less than or equal operators.
The following example shows this comparison:

SELECT Name,Age FROM Sample.Person
WHERE Age BETWEEN 18 AND 21
ORDER BY Age

This will return all records in the Sample.
Table of persons with age values between 18 and 21, including these values.
Note that the BETWEEN values must be specified in ascending order;
For example, predicates such as BETWEEN 21 AND 18 will return an empty string.
If the value of a scalar expression is not within the specified range, BETWEEN returns an empty string.

Like most predicates, BETWEEN can be inverted using the NOT logical operator.
Neither BETWEEN nor NOT BETWEEN can be used to return NULL fields.
The return NULL field uses IS NULL.
An example of NOT BETWEEN is as follows:

SELECT Name,Age FROM Sample.Person
WHERE Age NOT BETWEEN 20 AND 55
ORDER BY Age

This will return all records in the Sample.
The table for people with age values less than 20 or greater than 55 does not include these values.

Sort type

BETWEEN is usually used for numeric ranges sorted in numerical order.
However, BETWEEN can be used for collation sequence ranges for values of any data type.

BETWEEN uses the same collation type as the column it matches.
By default, the string data type is sorted as SQLUPPER, which is case insensitive.

If the query assigns a different collation type to a column, you must also apply this collation type to the BETWEEN substring.
The following example illustrates this:

In the following example, BETWEEN uses the default letter case collation SQLUPPER for the field, which is case insensitive.
It returns Name in alphabetical order than Home_State high, Home_State is in alphabetical order than Home_City high record:

SELECT Name,Home_State,Home_City
FROM Sample.Person
WHERE Home_State BETWEEN Name AND Home_City
ORDER BY Home_State

In the following example, the BETWEEN string comparison is not case sensitive because home_ The state field is defined as SQLUPPER.
This means that low val and high val are functionally the same. Select 'MA' in any letter:

SELECT Name,Home_State FROM Sample.Person
WHERE Home_State
   BETWEEN 'MA' AND 'Ma'
ORDER BY Home_State

In the following example, the% SQLSTRING sort function makes the BETWEEN string comparison case sensitive.
It chooses those homes_ Records with state values from 'MA' to 'MA', including 'MA', 'MD', 'ME', 'MO', 'MS' and' MT ':

SELECT Name,Home_State FROM Sample.Person
WHERE %SQLSTRING(Home_State) 
   BETWEEN %SQLSTRING('MA') AND %SQLSTRING('Ma')
ORDER BY Home_State

In the following example, the BETWEEN string comparison is case insensitive and ignores spaces and punctuation:

SELECT Name FROM Sample.Person
WHERE %STRING(Name) BETWEEN %SQLSTRING('OA') AND %SQLSTRING('OZ')
ORDER BY Name

The following example shows the BETWEEN used in the internal join operation ON clause.
It is performing a case insensitive string comparison:

SELECT P.Name AS PersonName,E.Name AS EmpName 
FROM Sample.Person AS P INNER JOIN Sample.Employee AS E
ON P.Name BETWEEN 'an' AND 'ch' AND P.Name=E.Name

%SelectMode

If% SelectMode is set to a value other than the logical format, the BETWEEN predicate value must be specified in% SelectMode format (ODBC or Display).
This applies primarily to date, time, and IRIS formatted lists (% List).
Specifying predicate values in logical format usually results in SQLCODE errors.
For example, SQLCODE -146 "cannot convert date input to a valid logical date value".

In the following dynamic SQL example, the BETWEEN predicate must specify a date in the format% SelectMode=1 (ODBC):

ClassMethod Between()
{
	s q1 = "SELECT Name,DOB FROM Sample.Person "
	s q2 = "WHERE DOB BETWEEN '1950-01-01' AND '1960-01-01'"
	s myquery = q1_q2
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=1
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
}
DHC-APP>d ##class(PHA.TEST.SQLCommand).Between()
Name    DOB
Houseman,Martin D.      1955-09-25
Ingrahm,Yan S.  1954-06-15
Smith,Elvis Y.  1955-06-29
Gore,Alfred M.  1958-09-15
Yoders,Liza U.  1959-06-05
Ng,Liza Z.      1955-10-05
Yeats,Debby G.  1951-12-06
Zweifelhofer,Zelda J.   1954-02-19
Solomon,Emily D.        1953-01-28
Isaacs,Elvis V. 1952-04-05
Pantaleo,Robert U.      1950-03-29
Zampitello,Josephine Q. 1953-08-14
Xiang,Molly F.  1953-03-21
Nichols,Heloisa M.      1957-07-19
Hertz,Uma C.    1954-07-25
LaRocca,David X.        1956-01-11
Houseman,Alice R.       1957-12-07
Alton,Phil T.   1953-02-25
Davis,Jane E.   1953-07-28
Vanzetti,Alexandra O.   1953-12-29
Uhles,Dmitry P. 1951-08-23
Jafari,Christine Z.     1950-04-11
 
22 Rows(s) Affected
End of data

Tags: Database SQL

Posted on Sun, 05 Dec 2021 08:23:18 -0500 by yumico23