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 --.

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.

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 --.

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

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.

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

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:

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}

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>

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).