2 Basic Elements of Oracle CQL

This chapter provides a reference for the basic Oracle CQL elements such as, data types, literals, nulls, etc.

2.1 Data Types

Each value manipulated by GGSA has a data type. The data type of a value associates a fixed set of properties with the value. These properties cause GGSA to treat values of one data type differently from values of another. For example, you can add values of INTEGER data type, but not values of CHAR data type. When you create a stream, you must specify a data type for each of its elements.

For more information, see:

• Oracle CQL Built-in Data Types

• Data Type Comparison Rules

• Literals

• Format Models

datatype::=

variable_length_datatype::=

fixed_length_datatype::=

2.1.1 Oracle CQL Built-in Data Types

Table 2-1 summarizes Oracle CQL built-in data types. Refer to the syntax in the preceding sections for the syntactic elements.

Consider these data type and data type literal restrictions when defining event types.

Table 2-1 Oracle CQL Built-in Data Type Summary

Oracle CQL Data Type	Description
BIGINT	Fixed-length number equivalent to a Java Long type. For more information, see Numeric LiteralsNumeric LiteralsNumeric LiteralsNumeric LiteralsNumeric Literals.
BOOLEAN	Fixed-length boolean equivalent to a Java Boolean type. Valid values are true or false.
CHAR[(size)] Oracle CQL supports single-dimension arrays only.	Variable-length character data of length size characters. Maximum size is 4096 characters. Default and minimum size is 1 character. For more information, see Text Literals.
DOUBLE	Fixed-length number equivalent to a Java double type. For more information, see Numeric Literals.
FLOAT	Fixed-length number equivalent to a Java float type. For more information, see Numeric Literals.
INTEGER	Fixed-length number equivalent to a Java int type. For more information, see Numeric Literals.
INTERVAL	Fixed-length INTERVAL data type specifies a period of time. GGSA supports DAY TO SECOND and YEAR TO MONTH. Maximum length is 64 bytes. This corresponds to a Java long type. For more information, see Interval Literals.
TIMESTAMP	Fixed-length TIMESTAMP data type stores a datetime literal that conforms to one of the java.text.SimpleDateFormat format models that Oracle CQL supports. Maximum length is 64 bytes. For more information, see Datetime Literals.

2.2 Data Type Comparison Rules

This section describes how GGSA compares values of each data type.

2.2.1 Numeric Values

A larger value is considered greater than a smaller one. All negative numbers are less than zero and all positive numbers. Thus, -1 is less than 100; -100 is less than -1.

2.2.2 Date Values

A later date is considered greater than an earlier one. For example, the date equivalent of '29-MAR-2005' is less than that of '05-JAN-2006' and '05-JAN-2006 1:35pm' is greater than '05-JAN-2005 10:09am'.

2.2.3 Character Values

Oracle CQL supports Lexicographic sort based on dictionary order.

Internally, Oracle CQL compares the numeric value of the char. Depending on the encoding used, the numeric values will differ, but in general, the comparison will remain the same. For example:

'a' < 'b'
'aa' < 'ab'
'aaaa' < 'aaaab'

2.2.4 Data Type Conversion

Generally an expression cannot contain values of different data types. For example, an arithmetic expression cannot multiply 5 by 10 and then add 'JAMES'. However, GGSA supports both implicit and explicit conversion of values from one data type to another.

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

• Oracle CQL statements are easier to understand when you use explicit data type conversion functions.

• Implicit data type conversion can have a negative impact on performance.

• Implicit conversion depends on the context in which it occurs and may not work the same way in every case.

• Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

This section describes:

• Implicit Data Type Conversion

• Explicit Data Type Conversion

• SQL Data Type Conversion

2.2.4.1 Implicit Data Type Conversion

GGSA automatically converts a value from one data type to another when such a conversion makes sense.

Table 2-2 is a matrix of Oracle implicit conversions. The table shows all possible conversions (marked with an X). Unsupported conversions are marked with a --.

Table 2-2 Implicit Type Conversion Matrix

from/to	to CHAR	to BYTE	to BOOLEAN	to INTEGER	to DOUBLE	to BIGINT	to FLOAT	to TIMESTAMP	to INTERVAL
from CHAR	--	--	--	--	--	--	--	X	--
from BYTE	X	--	--	--	--	--	--	--	--
from BOOLEAN	--	--	X	--	--	--	--	--	--
from INTEGER	X	--	--	--	X	X	X	--	--
from DOUBLE	X	--	--	--	X	--	--	--	--
from BIGINT	X	--	--	--	X	--	X	--	--
from FLOAT	X	--	--	--	X	--	--	--	--
from TIMESTAMP	X	--	--	--	--	--	--	--	--
from INTERVAL	X	--	--	--	--	--	--	--	--

The following rules govern the direction in which GGSA makes implicit data type conversions:

• During SELECT FROM operations, GGSA converts the data from the stream to the type of the target variable if the select clause contains arithmetic expressions or condition evaluations.

  For example, implicit conversions occurs in the context of expression evaluation, such as c1+2.0, or condition evaluation, such as c1 < 2.0, where c1 is of type INTEGER.

• Conversions from FLOAT to BIGINT are exact.

• Conversions from BIGINT to FLOAT are inexact if the BIGINT value uses more bits of precision that supported by the FLOAT.

• When comparing a character value with a TIMESTAMP value, GGSA converts the character data to TIMESTAMP.

• When you use a Oracle CQL function or operator with an argument of a data type other than the one it accepts, GGSA converts the argument to the accepted data type wherever supported.

• When making assignments, GGSA converts the value on the right side of the equal sign (=) to the data type of the target of the assignment on the left side.

• During concatenation operations, GGSA converts from non-character data types to CHAR.

• During arithmetic operations on and comparisons between character and non-character data types, GGSA converts from numeric types to CHAR as Table 2-2 shows.

2.2.4.2 Explicit Data Type Conversion

You can explicitly specify data type conversions using Oracle CQL conversion functions. Table 2-3 shows Oracle CQL functions that explicitly convert a value from one data type to another. Unsupported conversions are marked with a --.

Table 2-3 Explicit Type Conversion Matrix

from/to	to CHAR	to BYTE	to BOOLEAN	to INTEGER	to DOUBLE	to BIGINT	to FLOAT	to TIMESTAMP	to INTERVAL
from CHAR	--	hextoraw	--	--	--	--	--	to_timestamp	--
from BYTE	--	rawtohex	--	--	--	--	--	--	--
from BOOLEAN	--	--	--	--	--	--	--	--	--
from INTEGER	to_char	--	to_boolean	--	to_double	to_bigint	to_float	--	--
from DOUBLE	to_char	--	--	--	--	--	--	--	--
from LONG	--	--	--	--	--	--	--	to_timestamp	--
from BIGINT	to_char	--	to_boolean	--	to_double	--	to_float	--	--
from FLOAT	to_char	--	--	--	to_double	--	--	--	--
from TIMESTAMP	to_char	--	--	--	--	--	--	--	--
from INTERVAL	to_char	--	--	--	--	--	--	--	--

2.2.4.3 SQL Data Type Conversion

Using an Oracle CQL processor, you can specify a relational database table as an event source. You can query this event source, join it with other event sources, and so on. When doing so, you must observe the SQL and data type equivalents that GGSA supports.

2.3 Literals

The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JACK', 'BLUE ISLAND', and '101' are all text literals; 5001 is a numeric literal.

GGSA supports the following types of literals in Oracle CQL statements:

• Text Literals

• Numeric Literals

• Datetime Literals

• Interval Literals.

2.3.1 Text Literals

Use the text literal notation to specify values whenever const_string, quoted_string_double_quotes, or quoted_string_single_quotes appears in the syntax of expressions, conditions, Oracle CQL functions, and Oracle CQL statements in other parts of this reference. This reference uses the terms text literal, character literal, and string interchangeably.

Text literals are enclosed in single or double quotation marks so that GGSA can distinguish them from schema object names.

You may use single quotation marks (') or double quotation marks ("). Typically, you use double quotation marks. However, for certain expressions, conditions, functions, and statements, you must use the quotation marks as specified in the syntax given in other parts of this reference: either quoted_string_double_quotes or quoted_string_single_quotes.

If the syntax uses simply const_string, then you can use either single or double quotation marks.

If the syntax uses the term char, then you can specify either a text literal or another expression that resolves to character data. When char appears in the syntax, the single quotation marks are not used.

GGSA supports Java localization. You can specify text literals in the character set specified by your Java locale.

For more information, see const_string

2.3.2 Numeric Literals

Use numeric literal notation to specify fixed and floating-point numbers.

2.3.2.1 Integer Literals

You must use the integer notation to specify an integer whenever integer appears in expressions, conditions, Oracle CQL functions, and Oracle CQL statements described in other parts of this reference.

The syntax of integer follows:

integer::=

where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

An integer can store a maximum of 32 digits of precision.

Here are some valid integers:

7
+255

2.3.2.2 Floating-Point Literals

You must use the number or floating-point notation to specify values whenever number or n appears in expressions, conditions, Oracle CQL functions, and Oracle CQL statements in other parts of this reference.

The syntax of number follows:

number::=

where

• + or - indicates a positive or negative value. If you omit the sign, then a positive value is the default.

• digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.

• f or F indicates that the number is a 32-bit binary floating point number of type FLOAT.

• d or D indicates that the number is a 64-bit binary floating point number of type DOUBLE. pcbpel/cep/src/oracle/cep/common/Constants.BIGINT_LENGTH

  If you omit f or F and d or D, then the number is of type INTEGER.

  The suffixes f or F and d or D are supported only in floating-point number literals, not in character strings that are to be converted to INTEGER. For example, if GGSA is expecting an INTEGER and it encounters the string '9', then it converts the string to the Java Integer 9. However, if GGSA encounters the string '9f', then conversion fails and an error is returned.

A number of type INTEGER can store a maximum of 32 digits of precision. If the literal requires more precision than provided by BIGINT or FLOAT, then GGSA truncates the value. If the range of the literal exceeds the range supported by BIGINT or FLOAT, then GGSA raises an error.

If your Java locale uses a decimal character other than a period (.), then you must specify numeric literals with 'text' notation. In such cases GGSA automatically converts the text literal to a numeric value.

Note:

You cannot use this notation for floating-point number literals.

For example, if your Java locale specifies a decimal character of comma (,), specify the number 5.123 as follows:

'5,123'

Here are some valid NUMBER literals:

25
+6.34
0.5
-1

Here are some valid floating-point number literals:

25f
+6.34F
0.5d
-1D

2.3.3 Datetime Literals

GGSA supports datetime data type TIMESTAMP.

Datetime literals must not exceed 64 bytes.

All datetime literals must conform to one of the java.text.SimpleDateFormat format models that Oracle CQL supports. For more information, see Datetime Format Models.

For example, if your XML event uses an XSD, Oracle CQL cannot parse the MyTimestamp element.

Oracle recommends that you define your XSD to replace xsd:dateTime with xsd:string.

Using the XSD, Oracle CQL can process events as long as the Timestamp element's String value conforms to the java.text.SimpleDateFormat format models that Oracle CQL supports. For more information, see Datetime Format Models.

2.3.4 Interval Literals

An interval literal specifies a period of time. GGSA supports interval literal DAY TO SECOND. This literal contains a leading field and may contain a trailing field. The leading field defines the basic unit of date or time being measured. The trailing field defines the smallest increment of the basic unit being considered. Part ranges (such as only SECOND or MINUTE to SECOND) are not supported.

Interval literals must not exceed 64 bytes.

2.3.4.1 INTERVAL DAY TO SECOND

Stores time in terms of days, hours, minutes, and seconds.

Specify DAY TO SECOND interval literals using the following syntax:

interval_value::=

where const_string is a TIMESTAMP value that conforms to the appropriate datetime format model (see Datetime Format Models).

Restriction on the Leading Field:

If you specify a trailing field, then it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid. As a result of this restriction, if SECOND is the leading field, the interval literal cannot have any trailing field.

The valid range of values for the trailing field are as follows:

• SECOND: 0 to 59.999999999

Examples of the various forms of INTERVAL DAY TO SECOND literals follow:

Form of Interval Literal	Interpretation
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)	4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.

You can add or subtract one DAY TO SECOND interval literal from another DAY TO SECOND literal and compare one interval literal to another. In this example, stream tkdata2_SIn1 has schema (c1 integer, c2 interval).

<query id="tkdata2_q295"><![CDATA
select * from tkdata2_SIn1 where (c2 + INTERVAL "2 1:03:45.10" DAY TO SECOND) > INTERVAL "6 12:23:45.10" DAY TO SECOND
]]></query>

Using INTERVAL DAY TO SECOND in the define clause of pattern match:

query 'select its.itemId from ch0 
MATCH_RECOGNIZE ( 
PARTITION BY itemId 
MEASURES A.itemId as itemId
PATTERN (A B* C) 
DEFINE A AS (A.temp >= 25), 
B AS ((B.temp >= 25) and 
(to_timestamp(B.element_time) - to_timestamp(A.element_time) < 
INTERVAL "00:00:05.00" HOUR TO SECOND)), 
C AS (to_timestamp(C.element_time) - to_timestamp(A.element_time) 
>= INTERVAL "00:05.00" MINUTE TO SECOND)
) as its'

Input:

send [itemId=2 temp=30]
send [itemId=2 temp=55]
thread:sleep 5000
send [itemId=2 temp=125]

Output:

-> insert event: {itemId=2}

2.3.4.2 INTERVAL YEAR TO MONTH

Stores time in terms of years and months.

Examples of the various forms of INTERVALYEARTOMONTH literals follow:

Form of Interval Literal	Interpretation
INTERVAL "12-10" YEAR TO MONTH	12 years and 10 months.

Note:

If used in the query DDL, the INTERVAL YEAR TO MONTH notation is used to specify constant in the CQL query. Specify a constant interval value instead of a variable.

2.4 Format Models

A format model is a character literal that describes the format of datetime or numeric data stored in a character string. When you convert a character string into a date or number, a format model determines how GGSA interprets the string. The following format models are relevant to Oracle CQL queries:

• Number Format Models

• Datetime Format Models.

2.4.1 Number Format Models

You can use number format models in the following functions:

• In the function to translate a value of int data type to bigint data type.

• In the to_float function to translate a value of int or bigint data type to float data type

2.4.2 Datetime Format Models

Oracle CQL supports the format models that the java.text.SimpleDateFormat specifies.

Table 2-4 lists the java.text.SimpleDateFormat models that Oracle CQL uses to interpret TIMESTAMP literals. For more information, see Datetime Literals.

Table 2-4 Datetime Format Models

Format Model	Example
MM/dd/yyyy HH:mm:ss Z	11/21/2005 11:14:23 -0800
MM/dd/yyyy HH:mm:ss z	11/21/2005 11:14:23 PST
MM/dd/yyyy HH:mm:ss	11/21/2005 11:14:23
MM-dd-yyyy HH:mm:ss	11-21-2005 11:14:23
dd-MMM-yy	15-DEC-01
yyyy-MM-dd'T'HH:mm:ss	2005-01-01T08:12:12

You can use a datetime format model in the following functions:

• to_timestamp: to translate the value of a char data type to a TIMESTAMP data type.

2.5 Nulls

If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in tuples of any data type that are not restricted by primary key integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.

GGSA treats a character value with a length of zero as it is, not like SQL. However, do not use null to represent a numeric value of zero, because they are not equivalent.

Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.

For more information, see:

• nvl.

• out_of_line_constraint.

2.5.1 Nulls in Oracle CQL Functions

All scalar functions (except nvl and concat) return null when given a null argument. You can use the nvl function to return a value when a null occurs. For example, the expression NVL(commission_pct,0) returns 0 if commission_pct is null or the value of commission_pct if it is not null.

Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.

2.5.2 Nulls with Comparison Conditions

To test for nulls, use only the null comparison conditions (see null_conditions::=). If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, GGSA considers two nulls to be equal when evaluating a decode expression. See decode::= for syntax and additional information.

2.5.3 Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no tuples. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Table 2-5 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

Table 2-5 Conditions Containing Nulls

Condition	Value of A	Evaluation
a IS NULL	10	FALSE
a IS NOT NULL	10	TRUE
a IS NULL	NULL	TRUE
a IS NOT NULL	NULL	FALSE
a = NULL	10	FALSE
a != NULL	10	FALSE
a = NULL	NULL	FALSE
a != NULL	NULL	FALSE
a = 10	NULL	FALSE
a != 10	NULL	FALSE

For more information, see Null Conditions.

2.6 Comments

Oracle CQL does not support comments.

2.7 Aliases

Oracle CQL allows you to define aliases (or synonyms) to simplify and improve the clarity of your queries.

This section describes:

• Defining Aliases Using the AS Operator

2.7.1 Defining Aliases Using the AS Operator

Using the AS operator, you can specify an alias in Oracle CQL for queries, relations, streams, and any items in the SELECT list of a query.

This section describes:

• Aliases in the relation_variable Clause

• Aliases in Window Operators.

2.7.1.1 Aliases in the relation_variable Clause

You can use the relation_variable clause AS operator to define an alias to label the immediately preceding expression in the select list so that you can reference the result by that name. The alias effectively renames the select list item for the duration of the query. You can use an alias in the ORDER BY clause, but not other clauses in the query.

The following example shows how to define alias badItem for a stream element its.itemId in a SELECT list and alias its for a MATCH_RECOGNIZE clause.

<query id="detectPerish"><![CDATA[ 
  select its.itemId as badItem
  from tkrfid_ItemTempStream MATCH_RECOGNIZE (
      PARTITION BY itemId
      MEASURES A.itemId as itemId
      PATTERN (A B* C)
      DEFINE
          A  AS  (A.temp >= 25),
          B  AS  ((B.temp >= 25) and (to_timestamp(B.element_time) - to_timestamp(A.element_time) < INTERVAL "0 00:00:05.00" DAY TO SECOND)),
          C  AS  (to_timestamp(C.element_time) - to_timestamp(A.element_time) >= INTERVAL "0 00:00:05.00" DAY TO SECOND)
  ) as its
]]></query>

2.7.1.2 Aliases in Window Operators

You can use the AS operator to define an alias to label the immediately preceding window operator so that you can reference the result by that name.

You may not use the AS operator within a window operator but you may use the AS operator outside of the window operator.

The following example shows how to define aliases bid and ask after partitioned range window operators.

<query id="Rule1"><![CDATA[
SELECT
    bid.id as correlationId
    bid.cusip as cusip
    max(bid.b0) as bid0
    bid.srcid as bidSrcId,
    bid.bq0 as bid0Qty,
    min(ask.a0) as ask0,
    ask.srcid as askSrcId,
    ask.aq0 as ask0Qty
FROM
    stream1[PARTITION by bid.cusip rows 100 range 4 hours] as bid,
    stream2[PARTITION by ask.cusip rows 100 range 4 hours] as ask
GROUP BY
    bid.id, bid.cusip, bid.srcid,bid.bq0, ask.srcid, ask.aq0
]]></query> 

For more information, see Stream-to-Relation Operators (Windows).