SQL Expressions: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 28: Line 28:
||
||
Returns given value rounded to the nearest equal or larger integer. The data type should be one of the numeric data types. If the value is null, then the result is also null.
Returns given value rounded to the nearest equal or larger integer. The data type should be one of the numeric data types. If the value is null, then the result is also null.
|-
||Coalesce
||Coalesce
||
||

Revision as of 00:13, 18 February 2022

SQL expressions are special expressions in the expression language, which are converted into SQL and evaluated in an external system that supports SQL (e.g., Snowflake, SQL Server, Databricks, Redshift). Only a subset of expression language functionalities are supported by SQL expressions (which are explained in this page).

  • Arithmetics operators: Addition (+), Subtraction (-), Multiplication (*), Division (/), Remainder (%)
  • Comparison operators: ==, <, <=, >, >=, !=.
  • Logical operators: && (AND), || (OR), ! (NOT) are used to combine expressions that return boolean values.
  • Data types: strings ("this is a string"), integers (123), decimal numbers (123.45), booleans (true, false)
  • null value (null)
Function Parameters Description
CaseWhen

Goes through conditions and returns a value when the first condition is met, similar to an if-then-else. Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the else-expression. Consists of any number of pairs of condition and value expressions followed by an optional else expression.

CaseWhen(Column("a") == null, 1, Column("a") < 1.0, 2, 3)
Returns 1 if the value of column "a" is null.
Returns 2 if the value of column "a" is less than 1.0.
Returns 3 otherwise.
Ceiling number

Returns given value rounded to the nearest equal or larger integer. The data type should be one of the numeric data types. If the value is null, then the result is also null.

Coalesce

Returns the first non-null parameter it is given. There can be any number of parameters. If all parameters are null, returns null.

Column Return the value of given column.
Concat

Return the concatenated string value of given values.

Floor

Returns given value rounded to the nearest equal or smaller integer. The data type should be one of the numeric data types. If the value is null, then the result is also null.

Variable

Returns the value of given variable. Supports number, string and boolean values.

Examples:

let x = "124256122";
CreateRedshiftConnection().Table("eventlog_o2c_events").Where(#sql{Column("CaseId")==Variable("x")}).Collect()

Returns cases having id equal to "124256122".
DateDiff

Calculates the count of the specified datepart boundaries crossed between the specified dates. Parameters:

  1. datepart: Units in which datediff reports the difference between the dates. Supported date parts: second, minute, hour, day, week, quarter, month, year
  2. startDate: Starting timestamp.
  3. endDate: Ending timestamp.
Day Returns the days part of given timestamp.
Hour Returns the hours part of given timestamp.
Millisecond Returns the milliseconds part of given timestamp.
Minute Returns the minutes part of given timestamp.
Month Returns the months part of given timestamp.
Second Returns the seconds part of given timestamp.
Year Returns the years part of given timestamp.