Comparison operators (=, <, >, etc.)

The following operators are supported:

	=	equal to
	>	greater than
	<	less than
	>=	greater than or equal to
	<=	less than or equal to
	!=	not equal to
	<>	not equal to
	!>	not greater than
	!<	not less than

Any blank spaces around operators are ignored (for instance, "> =" is equivalent to ">="). In comparing character data, "<" means earlier and ">" means later in the alphabet. In comparing dates, "<" means earlier and ">" means later. Be sure to put single quotation marks (') around all text and date/time columns. NOT negates an expression.

Example: radvel is not NULL

Inclusive ranges (BETWEEN and NOT BETWEEN)

You can use the BETWEEN keyword to search for the lower and upper value as well as the values in between them. NOT BETWEEN finds all rows not inside the range.

Example: ra between 12 and 14
Example: dec not between -90 and 0

Lists and Set Membership (IN, NOT IN)

The IN keyword allows you to select values that match any one of a list of values. NOT IN selects values which do not match values in the list. The parentheses are required.

Example: Hubble_Type in('Sa', 'Sb', 'Sc')

This is more concise than:

Hubble_Type = 'Sa' or Hubble_Type = 'Sb' or Hubble_Type = 'Sc'

Pattern and character string matching (LIKE, NOT LIKE)

The LIKE keyword is used to select rows containing column values that match specified portions of character strings. LIKE is used with character strings and text columns. LIKE accept 2 wildcards:

• %    (percent) any string of 0 or more characters
• _    (underscore) any single character

NOT LIKE can be used with the same wildcards.

Character strings must be enclosed in single or double quotes. Do not attempt to use back-quotes (`).

Examples:

au_lname LIKE '%son' - selects names ending with "son"; returns "albertson" as well as "son"

au_lname LIKE '%en%' - selects any name with "en" in it; returns "english," "steven," "dennis," etc

au_lname LIKE '_ars' - 4 letters ending with "ars"; returns "lars" but not "sears"

IMPORTANT NOTE: The only WHERE conditions that you can use on character columns are "=" (equals), LIKE or NOT LIKE. There are numerous character columns in the astronomical catalogs maintained at IPAC. Use the "COLUMN CONSTRAINTS / OUTPUT COLUMN SELECTION" panel to view the data type for any column of interest.

NOTE: When a literal string equality is desired, it is recommended that you use "=" in the constraint. For example:

k_mrg_flg='200302'

However, to find records that contain substring matches (using wildcards), you should use LIKE. For example, to locate k_mrg_flg values with any characters followed by '2', you might try the following:

k_mrg_flg='%2'

Although this is syntactically valid SQL, it will result in no matching records because these flags contain only numeric characters such as '200302'. To get the constraint you need on this character column you should type

k_mrg_flg LIKE '%2'

Note that '%' is the wildcard used with the LIKE condition (not "*").

NULLs and Unknown Values (IS NULL and IS NOT NULL)

An entry of NULL in a data table means that there is no entry (e.g., missing data). NULL is not synonymous with 'zero' or 'blank'. 'NULL' and 'null' are equivalent. Examples:

Bmag is null
Bmag is not null

Note that some data tables may have columns where null values are not permitted. These are indicated in the column descriptions.

Logical Operators (AND, OR, NOT)

The logical operators AND, OR and NOT are used to connect search conditions in the WHERE clause. When more than 1 logical operator is used in a statement, NOT is evaluated 1st, then AND, and finally OR. You can use parentheses to change the order of evaluation within a WHERE clause.

Example:not fnu_60/fnu_100 > 0.25

Example:(fqual_12 = 3 or fqual_60 = 3) and major <5.0

Mathematical Computations

You can perform simple computations with data from numeric columns or on numeric constants in a select list using standard arithmetic operators: +, -, /, *, and % (modulo). When there is more than 1 arithmetic operator in an expression, multiplication, division, and modulo are calculated first, followed by subtraction and addition. Expressions within parentheses take precedence over all other operations.

The following mathematical functions are available:

abs(numeric_expression)	Absolute Value
acos(float_expr)	Arc Cosine
asin(float_expr)	Arc Sine
atan(float_expr)	Arc Tangent
cos(float_expr)	Cosine
exp(float_expr)
floor(float_expr)
log(10, float_expr)*	Logarithm (base 10)
power(numeric_expr,power)	numeric_expr raised to the power of power
rand([integer_expr])
round(numeric_expr, integer_expr)	Returns the rounded value of the first expression, out to integer_expr digits
sin(float_expr)	Sine
sqrt(float_expr)	Square root
tan(float_expr)	Tangent

Date Constraints

Allowed formats for the "date" type is yyyy-mm-dd, i.e., year first, then a two-digit month, followed by a two-digit day.

date = '1997-10-24'
date between '1997-10-24' and '1997-10-29'

IMPORTANT NOTE: For technical reasons, IRSA supports two date formats. If the format you are using fails, please contact the IRSA HelpDesk for assistance. Also, no white characters are allowed in additional constraints.