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 table below, the vertical axis represents the left hand operand, the horizontal axis represents the right hand operand.
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:
|
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 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.
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 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.
If a character string is compared with a value of another type, WebSphere Message Broker attempts to cast the character string into a value of the same data type as the other value.
'1234'> 4567
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.
Body.Trade.Price> 100
Body.Trade.Price> 100.00