Implicit CASTs for comparisons

The standard SQL comparison operators>, <,>=, <=, =, <> are supported for comparing two values in ESQL.

When the data types of the two values are not the same, one of them can be implicitly cast to the type of the other to allow the comparison to proceed. In the following table, the vertical axis represents the left hand operand, the horizontal axis represents the right hand operand.

L means that the right hand operand is cast to the type of the left hand operand before comparison; R means the opposite; X means that no implicit casting takes place; a blank means that comparison between the values of the two data types is not supported.
  ukn bln int float dec char time gtm date ts gts ivl blob bit
ukn                            
bln   X       L                
int     X R R L                
float     L X L L                
dec     L R X L                
chr   R R R R X R R R R R R1 R R
tm           L X L            
gtm           L R X            
dt           L     X R2 R2      
ts           L     L2 X L      
gts           L     L2 R X      
ivl           L1           X    
blb           L             X  
bit           L               X
Notes:
  1. When casting from a character string to an interval, the character string must be of the format INTERVAL '<values>' <qualifier>. The format <values>, which is allowede for an explicit CAST, is not allowed here because no qualifier external to the string is supplied.
  2. When casting from a DATE to a TIMESTAMP or GMTTIMESTAMP, the time portion of the TIMESTAMP is set to all zero values (00:00:00). This is different from the behavior of the explicit cast, which sets the time portion to the current time.

Numeric types

The comparison operators operate on all three numeric types.

Character strings

You cannot define an alternative collation order that, for example, collates upper and lowercase characters equally.

When comparing character strings, trailing blanks are not significant, so the comparison 'hello' = 'hello ' returns true.

Datetime values

Datetime values are compared in accordance with the natural rules of the Gregorian calendar and clock.

You can compare the time zone you are working in with the GMT time zone. The GMT time zone is converted into a local time zone based on the difference between your local time zone and the GMT time specified. When you compare your local time with the GMT time, the comparison is based on the difference at a given time on a given date.

Conversion is always based on the value of LOCAL_TIMEZONE. This is because GMT timestamps are converted to local timestamps only if it can be done unambiguously. Converting a local timestamp to a GMT timestamp has difficulties around the daylight saving cut-over time, and converting between times and GMT times (without date information) has to be done based on the LOCAL_TIMEZONE value, because you cannot specify which time zone difference to use otherwise.

Booleans

Boolean values can be compared using all the normal comparison operators. The TRUE value is defined to be greater than the FALSE value. Comparing either value to the UNKNOWN Boolean value (which is equivalent to NULL) returns an UNKNOWN result.

Intervals

Intervals are compared by converting the two interval values into intermediate representations, so that both intervals have the same interval qualifier. Year-month intervals can be compared only with other year-month intervals, and day-second intervals can be compared only with other day-second intervals.

For example, if an interval in minutes, such as INTERVAL '120' MINUTE is compared with an interval in days to seconds, such as INTERVAL '0 02:01:00', the two intervals are first converted into values that have consistent interval qualifiers, which can be compared. So, in this example, the first value is converted into an interval in days to seconds, which gives INTERVAL '0 02:00:00', which can be compared with the second value.

Comparing character strings with other types

If a character string is compared with a value of another type, the integration node attempts to cast the character string into a value of the same data type as the other value.

For example, you can write an expression: 
'1234'> 4567
The character string on the left is converted into an integer before the comparison takes place. This behavior reduces some of the need for explicit CAST operators when comparing values derived from a generic XML message with literal values. (For details of explicit casts that are supported, see Supported casts.) It is this facility that allows you to write the following expression: 
Body.Trade.Quantity> 5000

In this example, the field reference on the left evaluates to the character string '1000' and, because this is being compared to an integer, that character string is converted into an integer before the comparison takes place.

You must still check whether the price field that you want interpreted as a decimal is greater than a given threshold. Make sure that the literal you compare it to is a decimal value and not an integer.

Consider the following example: 
Body.Trade.Price> 100
This comparison does not return the required or expected result, because the Price field is converted into an integer, and that conversion fails because the character string contains a decimal point. However, the following expression succeeds: 
Body.Trade.Price> 100.00