Go to main content
1/23
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Syntax Diagrams
What's New in This Guide
Part I Understanding Oracle CQL
1
Introduction to Oracle CQL
1.1
Fundamentals of Oracle CQL
1.1.1
Streams and Relations
1.1.1.1
Streams
1.1.1.1.1
Streams and Channels
1.1.1.1.2
Channel Schema
1.1.1.1.3
Querying a Channel
1.1.1.1.4
Controlling Which Queries Output to a Downstream Channel
1.1.1.2
Relations
1.1.1.3
Relations and Oracle Stream Analytics Tuple Kind Indicator
1.1.2
Relation-to-Relation Operators
1.1.3
Stream-to-Relation Operators (Windows)
1.1.3.1
Range, Rows, and Slide
1.1.3.1.1
Range, Rows, and Slide at Query Start-Up and for Empty Relations
1.1.3.2
Partition
1.1.3.3
Default Stream-to-Relation Operator
1.1.4
Relation-to-Stream Operators
1.1.4.1
Default Relation-to-Stream Operator
1.1.5
Stream-to-Stream Operators
1.1.6
Queries, Views, and Joins
1.1.7
Pattern Recognition
1.1.8
Event Sources and Event Sinks
1.1.8.1
Event Sources
1.1.8.2
Event Sinks
1.1.8.3
Connecting Event Sources and Event Sinks
1.1.9
Table Event Sources
1.1.9.1
Relational Database Table Event Sources
1.1.9.2
XML Table Event Sources
1.1.9.3
Function Table Event Sources
1.1.10
Table Event Sink
1.1.10.1
Spring Assembly File
1.1.10.2
Application Configuration File
1.1.11
Cache Event Sources
1.1.12
Functions
1.1.13
Time
1.2
Oracle CQL Statements
1.2.1
Lexical Conventions
1.2.2
Syntactic Shortcuts and Defaults
1.2.3
Documentation Conventions
1.3
Oracle CQL and SQL Standards
1.4
Oracle Stream Analytics Server
2
Basic Elements of Oracle CQL
2.1
Data Types
2.1.1
Oracle CQL Built-in Data Types
2.1.2
Handling Other Data Types Using Oracle CQL Data Cartridges
2.1.3
Handling Other Data Types Using a User-Defined Function
2.2
Data Type Comparison Rules
2.2.1
Numeric Values
2.2.2
Date Values
2.2.3
Character Values
2.2.4
Data Type Conversion
2.2.4.1
Implicit Data Type Conversion
2.2.4.2
Explicit Data Type Conversion
2.2.4.3
SQL Data Type Conversion
2.2.4.4
Oracle Data Cartridge Data Type Conversion
2.2.4.5
User-Defined Function Data Type Conversion
2.3
Literals
2.3.1
Text Literals
2.3.2
Numeric Literals
2.3.2.1
Integer Literals
2.3.2.2
Floating-Point Literals
2.3.3
Datetime Literals
2.3.4
Interval Literals
2.3.4.1
INTERVAL DAY TO SECOND
2.3.4.2
INTERVAL YEAR TO MONTH
2.4
Format Models
2.4.1
Number Format Models
2.4.2
Datetime Format Models
2.5
Nulls
2.5.1
Nulls in Oracle CQL Functions
2.5.2
Nulls with Comparison Conditions
2.5.3
Nulls in Conditions
2.6
Comments
2.7
Aliases
2.7.1
Defining Aliases Using the AS Operator
2.7.1.1
Aliases in the relation_variable Clause
2.7.1.2
Aliases in Window Operators
2.7.2
Defining Aliases Using the Aliases Element
2.7.2.1
How to Define a Data Type Alias Using the Aliases Element
2.8
Schema Object Names and Qualifiers
2.8.1
Schema Object Naming Rules
2.8.2
Schema Object Naming Guidelines
2.8.3
Schema Object Naming Examples
3
Pseudocolumns
3.1
Introduction to Pseudocolumns
3.2
ELEMENT_TIME Pseudocolumn
3.2.1
Understanding the Value of the ELEMENT_TIME Pseudocolumn
3.2.1.1
ELEMENT_TIME for a System-Timestamped Stream
3.2.1.2
ELEMENT_TIME for an Application-Timestamped Stream
3.2.1.2.1
Derived Timestamp Expression Evaluates to int or bigint
3.2.1.2.2
Derived Timestamp Expression Evaluates to timestamp
3.2.1.3
ELEMENT_TIME for an Inline CQL View
3.2.1.4
ELEMENT_TIME for a Subquery
3.2.2
Using the ELEMENT_TIME Pseudocolumn in Oracle CQL Queries
3.2.2.1
Using ELEMENT_TIME With SELECT
3.2.2.2
Using ELEMENT_TIME With GROUP BY
3.2.2.3
Using ELEMENT_TIME With PATTERN
3.3
ORA_QUERY_ID Pseudocolumn
4
Operators
4.1
Introduction to Operators
4.1.1
What You May Need to Know About Unary and Binary Operators
4.1.2
What You May Need to Know About Operator Precedence
4.2
Arithmetic Operators
4.3
Concatenation Operator
4.4
Alternation Operator
4.5
Range-Based Stream-to-Relation Window Operators
4.5.1
S[now]
4.5.1.1
Examples
4.5.2
S[range T]
4.5.2.1
Examples
4.5.3
S[range T1 slide T2]
4.5.3.1
Examples
4.5.4
S[range unbounded]
4.5.4.1
Examples
4.5.5
S[range C on E]
4.5.5.1
Examples
4.6
Tuple-Based Stream-to-Relation Window Operators
4.6.1
S [rows N]
4.6.1.1
Examples
4.6.2
S [rows N1 slide N2]
4.6.2.1
Examples
4.7
Partitioned Stream-to-Relation Window Operators
4.7.1
S [partition by A1,..., Ak rows N]
4.7.1.1
Examples
4.7.2
S [partition by A1,..., Ak rows N range T]
4.7.2.1
Examples
4.7.3
S [partition by A1,..., Ak rows N range T1 slide T2]
4.7.3.1
Examples
4.8
User-Defined Stream-to-Relation Window Operators
4.8.1
Implementing a User-Defined Window
4.8.1.1
How to Implement a User-Defined Generic Time Window
4.9
IStream Relation-to-Stream Operator
4.10
DStream Relation-to-Stream Operator
4.11
RStream Relation-to-Stream Operator
5
Expressions
5.1
Introduction to Expressions
5.2
aggr_distinct_expr
5.2.1
Examples
5.3
aggr_expr
5.3.1
Examples
5.4
arith_expr
5.4.1
Examples
5.5
arith_expr_list
5.5.1
Examples
5.6
case_expr
5.6.1
Examples
5.7
decode
5.7.1
Examples
5.8
func_expr
5.8.1
Examples
5.9
object_expr
5.9.1
Examples
5.10
order_expr
5.10.1
Examples
5.11
xml_agg_expr
5.11.1
Examples
5.12
xmlcolattval_expr
5.12.1
Examples
5.13
xmlelement_expr
5.13.1
Examples
5.14
xmlforest_expr
5.14.1
Examples
5.15
xml_parse_expr
5.15.1
Examples
6
Conditions
6.1
Introduction to Conditions
6.1.1
Condition Precedence
6.2
Comparison Conditions
6.3
Logical Conditions
6.4
LIKE Condition
6.4.1
Examples
6.5
Range Conditions
6.6
Null Conditions
6.7
Compound Conditions
6.8
IN Condition
6.8.1
Using IN and NOT IN as a Membership Condition
6.8.2
NOT IN and Null Values
7
Common Oracle CQL DDL Clauses
7.1
Introduction to Common Oracle CQL DDL Clauses
7.2
array_type
7.3
attr
7.4
attrspec
7.5
complex_type
7.6
const_bigint
7.7
const_int
7.8
const_string
7.9
const_value
7.10
identifier
7.11
l-value
7.12
methodname
7.13
non_mt_arg_list
7.14
non_mt_attr_list
7.15
non_mt_attrname_list
7.16
non_mt_attrspec_list
7.17
non_mt_cond_list
7.18
out_of_line_constraint
7.19
param_list
7.20
qualified_type_name
7.21
query_ref
7.22
time_spec
7.23
xml_attribute_list
7.24
xml_attr_list
7.25
xqryargs_list
Part II Functions
8
Built-In Single-Row Functions
8.1
Introduction to Oracle CQL Built-In Single-Row Functions
8.1
concat
8.2
hextoraw
8.3
length
8.4
lk
8.5
nvl
8.6
prev
8.7
rawtohex
8.8
systimestamp
8.9
to_bigint
8.10
to_boolean
8.11
to_char
8.12
to_double
8.13
to_float
8.14
to_timestamp
8.15
xmlcomment
8.16
xmlconcat
8.17
xmlexists
8.18
xmlquery
9
Built-In Aggregate Functions
9.1
Introduction to Oracle CQL Built-In Aggregate Functions
9.1.1
Built-In Aggregate Functions and the Where, Group By, and Having Clauses
9.1
avg
9.2
count
9.3
first
9.4
last
9.5
listagg
9.6
max
9.7
min
9.8
sum
9.9
xmlagg
10
Colt Single-Row Functions
10.1
Introduction to Oracle CQLBuilt-In Single-Row Colt Functions
10.1
beta
10.2
beta1
10.3
betaComplemented
10.4
binomial
10.5
binomial1
10.6
binomial2
10.7
binomialComplemented
10.8
bitMaskWithBitsSetFromTo
10.9
ceil
10.10
chiSquare
10.11
chiSquareComplemented
10.12
errorFunction
10.13
errorFunctionComplemented
10.14
factorial
10.15
floor
10.16
gamma
10.17
gamma1
10.18
gammaComplemented
10.19
getSeedAtRowColumn
10.20
hash
10.21
hash1
10.22
hash2
10.23
hash3
10.24
i0
10.25
i0e
10.26
i1
10.27
i1e
10.28
incompleteBeta
10.29
incompleteGamma
10.30
incompleteGammaComplement
10.31
j0
10.32
j1
10.33
jn
10.34
k0
10.35
k0e
10.36
k1
10.37
k1e
10.38
kn
10.39
leastSignificantBit
10.40
log
10.41
log10
10.42
log2
10.43
logFactorial
10.44
logGamma
10.45
longFactorial
10.46
mostSignificantBit
10.47
negativeBinomial
10.48
negativeBinomialComplemented
10.49
normal
10.50
normal1
10.51
normalInverse
10.52
poisson
10.53
poissonComplemented
10.54
stirlingCorrection
10.55
studentT
10.56
studentTInverse
10.57
y0
10.58
y1
10.59
yn
11
Colt Aggregate Functions
11.1
Introduction to Oracle CQL Built-In Aggregate Colt Functions
11.1.1
Oracle CQL Colt Aggregate Function Signatures and Tuple Arguments
11.1.2
Colt Aggregate Functions and the Where, Group By, and Having Clauses
11.1
autoCorrelation
11.2
correlation
11.3
covariance
11.4
geometricMean
11.5
geometricMean1
11.6
harmonicMean
11.7
kurtosis
11.8
lag1
11.9
mean
11.10
meanDeviation
11.11
median
11.12
moment
11.13
pooledMean
11.14
pooledVariance
11.15
product
11.16
quantile
11.17
quantileInverse
11.18
rankInterpolated
11.19
rms
11.20
sampleKurtosis
11.21
sampleKurtosisStandardError
11.22
sampleSkew
11.23
sampleSkewStandardError
11.24
sampleVariance
11.25
skew
11.26
standardDeviation
11.27
standardError
11.28
sumOfInversions
11.29
sumOfLogarithms
11.30
sumOfPowerDeviations
11.31
sumOfPowers
11.32
sumOfSquaredDeviations
11.33
sumOfSquares
11.34
trimmedMean
11.35
variance
11.36
weightedMean
11.37
winsorizedMean
12
java.lang.Math Functions
12.1
Introduction to Oracle CQL Built-In java.lang.Math Functions
12.1
abs
12.2
abs1
12.3
abs2
12.4
abs3
12.5
acos
12.6
asin
12.7
atan
12.8
atan2
12.9
cbrt
12.10
ceil1
12.11
cos
12.12
cosh
12.13
exp
12.14
expm1
12.15
floor1
12.16
hypot
12.17
IEEEremainder
12.18
log1
12.19
log101
12.20
log1p
12.21
pow
12.22
rint
12.23
round
12.24
round1
12.25
signum
12.26
signum1
12.27
sin
12.28
sinh
12.29
sqrt
12.30
tan
12.31
tanh
12.32
todegrees
12.33
toradians
12.34
ulp
12.35
ulp1
13
User-Defined Functions
13.1
Introduction to Oracle CQL User-Defined Functions
13.1.1
Types of User-Defined Functions
13.1.1.1
User-Defined Single-Row Functions
13.1.1.2
User-Defined Aggregate Functions
13.1.2
User-Defined Function Data Types
13.1.3
User-Defined Functions and the Oracle Stream Analytics Server Cache
13.2
Implementing a User-Defined Function
13.2.1
How to Implement a User-Defined Single-Row Function
13.2.2
How to Implement a User-Defined Aggregate Function
Part III Using Oracle CQL
14
Oracle CQL Queries, Views, and Joins
14.1
Introduction to Oracle CQL Queries, Subqueries, Views, and Joins
14.2
Queries
14.2.1
Query Building Blocks
14.2.1.1
Select, From, Where Block
14.2.1.2
Select Clause
14.2.1.3
From Clause
14.2.1.4
Where Clause
14.2.1.5
Group By Clause
14.2.1.6
Order By Clause
14.2.1.7
Having Clause
14.2.1.8
Binary Clause
14.2.1.9
IDStream Clause
14.2.2
Simple Query
14.2.3
Built-In Window Query
14.2.4
User-Defined Window Query
14.2.5
MATCH_RECOGNIZE Query
14.2.6
Relational Database Table Query
14.2.7
XMLTABLE Query
14.2.8
Function TABLE Query
14.2.9
Cache Query
14.2.10
Sorting Query Results
14.2.11
Detecting Differences in Query Results
14.2.12
Parameterized Queries
14.2.12.1
Parameterized Queries in Oracle CQL Statements
14.2.12.2
The bindings Element
14.2.12.3
Run-Time Query Naming
14.2.12.4
Lexical Conventions for Parameter Values
14.2.12.5
Parameterized Queries at Runtime
14.2.12.6
Replacing Parameters Programmatically
14.2.13
Subqueries
14.3
Views
14.3.1
Views and Joins
14.3.2
Views and Schemas
14.4
Joins
14.4.1
Inner Joins
14.4.2
Outer Joins
14.4.2.1
Left Outer Join
14.4.2.2
Right Outer Join
14.4.2.3
Outer Join Look-Back
14.5
Oracle CQL Queries and the Oracle Stream Analytics Server Cache
14.5.1
Creating Joins Against the Cache
14.5.1.1
Cache Key First and Simple Equality
14.5.1.2
No Arithmetic Operations on Cache Keys
14.5.1.3
No Full Scans
14.5.1.4
Multiple Conditions and Inequality
14.6
Oracle CQL Queries and Relational Database Tables
14.7
Oracle CQL Queries and Oracle Data Cartridges
15
Pattern Recognition With MATCH_RECOGNIZE
15.1
Understanding Pattern Recognition With MATCH_RECOGNIZE
15.1.1
MATCH_RECOGNIZE and the WHERE Clause
15.1.2
Referencing Singleton and Group Matches
15.1.3
Referencing Aggregates
15.1.3.1
Running Aggregates and Final Aggregates
15.1.3.2
Operating on the Same Correlation Variable
15.1.3.3
Referencing Variables That Have not Been Matched Yet
15.1.3.4
Referencing Attributes not Qualified by Correlation Variable
15.1.3.5
Using count With *, identifier.*, and identifier.attr
15.1.3.6
Using first and last
15.1.4
Using prev
15.2
MEASURES Clause
15.2.1
Functions Over Correlation Variables in the MEASURES Clause
15.3
PATTERN Clause
15.3.1
Pattern Quantifiers and Regular Expressions
15.3.2
Grouping and Alternation in the PATTERN Clause
15.4
DEFINE Clause
15.4.1
Functions Over Correlation Variables in the DEFINE Clause
15.4.2
Referencing Attributes in the DEFINE Clause
15.4.3
Referencing One Correlation Variable From Another in the DEFINE Clause
15.5
PARTITION BY Clause
15.6
ALL MATCHES Clause
15.7
WITHIN Clause
15.8
DURATION Clause
15.8.1
Fixed Duration Non-Event Detection
15.8.2
Recurring Non-Event Detection
15.9
INCLUDE TIMER EVENTS Clause
15.10
SUBSET Clause
15.11
MATCH_RECOGNIZE Examples
15.11.1
Pattern Detection
15.11.2
Pattern Detection With PARTITION BY
15.11.3
Pattern Detection With Aggregates
15.11.4
Pattern Detection With the WITHIN Clause
15.11.5
Fixed Duration Non-Event Detection
16
Oracle CQL Statements
16.1
Introduction to Oracle CQL Statements
16.1
Query
16.1.1
Query Semantics
16.1.2
Query Examples
16.2
View
Scripting on this page enhances content navigation, but does not change the content in any way.