About the Data Vault SQL Reference
Introduction to SQL Reference for Informatica Data Vault
Date and Time Arithmetic
WHERE Clauses
UNION Operator
Parameterized Query
Functions
- Functions Overview
- ABS
- AVG
- CEILING
- CHAR
- COALESCE
- CONCAT
- COUNT
- DATE
- DATE (Date and Time)
- DAY
- DEC
- DIGITS
- EXP
- EXTRACT
- FLOOR
- HOUR
- IFNULL
- INT
- LASTDAY
- LEFT
- LEN
- LN
- LOG10
- LOWER
- LTRIM
- MAX
- MICROSECOND
- MIN
- MINUTE
- MOD
- MONTH
- NANOSECOND
- NULLIF
- PI
- POSITION
- POSSTR
- POWER
- REPLACE
- RIGHT
- ROUND
- RTRIM
- SECOND
- SIGN
- SQRT
- SUBSTRING
- SUM
- TIME
- TODAY
- TRIM
- UPPER
- YEAR

Data Vault SQL Reference

6.4.3
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3 HotFix 1

Back Next

Simple Predicates

A simple predicate uses comparison operators in selection conditions:

The following table lists the simple predicates:

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

The following query displays all rows in the Supplier table containing a value of 20 in their status
column:

SELECT * 
FROM supplier
WHERE status = 20;

The following table displays the query results:

sno	sname	status	city
S1	SMITH	20	LONDON
S4	CLARK	20	LONDON

The WHERE clause in the next example compares a character string 'PARIS' with the city
column. The query below retrieves sno
(supplier number) and status
column data for all suppliers in Paris:

SELECT sno, status
FROM supplier
WHERE city = 'PARIS';

Note that character strings must be enclosed by single quotation marks ( 'x
' ).

The following table displays the results:

sno	status
S2	10
S3	30

When one column value is compared against another column value in a query and the column datatypes are different, the Data Vault Service tries to convert one of the data values to a datatype compatible with the other data value before performing the comparison. For example, if integer and float column values are compared, the INT data value is converted to a FLOAT value before the Data Vault Service performs the comparison.

Note that BLOB columns cannot be compared with other BLOB columns, or with columns of any other datatype.

Spaces in data values are considered when evaluating retrieval conditions. Right-justified data with leading spaces does not equal left-justified data with trailing spaces. For example, the string ‘Jones’ is read as a completely different value, depending on the number of preceding or trailing blank spaces attached.

The LIKE predicate operator allows string pattern matching without strict adherence to character string content and order. See the LIKE Predicates
section below for more information.

The < >
(not equal) comparison operator specifies records for retrieval that do not meet the specified condition. For example, the following query retrieves the pno
(part number) column values of all Part table records with a color
column value other than ‘RED’.

SELECT pno
FROM part
WHERE color <> 'RED';

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

Simple Predicates

Simple Predicates