Page tree

Table Transformer is powered by the AlaSQL library. Find the list of SQL functions available in the macro below.

Autocompletion

Use autocompletion for correct reference to column names, SQL functions and quick writing of SQL queries: start typing, e.g.,  T1.  and choose the column; or a function name to pick from the dropdown.

General SQL syntax 

Syntax typeDefinitionCharacter(s)Example
Table nameA title given to (a) table(s) placed inside Table Transformer in the order of placementT* - for all the tables in the macro body,
T1 - for the first table,
T2 - for the second one,
etc.

SELECT 'Column' FROM T*

SELECT 'Column name' FROM T1

SELECT 'Column Name' FROM T2

Column name

The title of a column from the source table utilized in the SQL query

Can be paired with Table name as a direct reference for a number of purposes: e.g. if column names  in T1 and T2 match

'  ' - Single quotes,

[  ] - Square brackets

'Column name'

[Column name]


T1.'Same Column name'

T2.'Same Column name'


If a column name contains a s ingle quote as its part: e.g. D'column, type \ before it to avoid the single quote becoming syntax markup: e.g. 'D\'column', [D\'column] or T1.'D\'column' - in the SQL query

String constantEncloses some text between double quotes further utilized in the SQL query" " - Double quotes

"Some constant"

"Free text"

Number constantEncloses a number further utilized in the SQL queryNo additional markup

2077


Date constantEncloses a date between double quotes further utilized in the SQL query" " - Double quotes"25.05.2022"
Multi-line comments

The text included directly in the SQL query as clarification, new info, etc., but not displayed in the macro output

/*  */ - Slash + Asterisk

/* Such comment is present directly in the SQL query but not included in the output */

Functions and keywords

Oops, it seems that you need to place a table or a macro generating a table within the Table Filter macro.

The table is being loaded. Please wait for a bit ...

Function / KeywordTypeDescription
ABS

MATH

Returns the absolute (positive) value of a number
ALL

LOGIC

Returns true if all of the subquery values meet the condition
AND

LOGIC

Includes rows where all the conditions separated by AND are TRUE
ANY

LOGIC

Returns true if any of the subquery values meet the condition
ARRAY

DATA_MANIPULATING

Creates an array
AS

DATA_MANIPULATING

Renames a column or table with an alias 
ASC

MATH 

ORDERING 

Ascending sorting order
AT

TIME


AVG

MATH 

AGGREGATION

Returns the average value of a group
BEGIN

LOGIC 

LOOP 

Starts code block for WHILE and IF statements
BETWEEN

MATH

Selects values within a given range
BY

AGGREGATION 

ORDERING 

Used with ORDER BY, GROUP BY
CASE

DATA_MANIPULATING 

LOGIC 

Creates different outputs based on conditions (see an example)
CAST

DATA_MANIPULATING 

CONVERTING 

Converts a value (of any type) into a specified datatype
CEIL

MATH

Gets the smallest integer which is greater than, or equal to, the specified numeric expression
CHAR

STRING_OPERATORS

Returns the character with the specified integer unicode
CHECK

LOGIC

Limits the value that can be placed in a column
COALESCE

LOGIC

Returns the first non-null value in a list
CONCAT

MERGING

Concatenates values
CONCAT_VIEW

MERGING

Concatenates values, preserving their original HTML format
CONCAT_VIEW_AGGR

MERGING 

AGGREGATION

Concatenates and aggregates values, preserving their original HTML format
CORRESPONDING

MERGING

Matches the columns in table-expressions by name and not by ordinal position
COUNT

MATH 

AGGREGATION 

Returns the number of rows that matches a specified criteria
CROSS

MERGING

One of the Join types. Returns all rows for all possible combinations of two tables
CUBE

MATH 

AGGREGATION 

Generates subtotals for all combinations of grouping columns specified in the GROUP BY clause
CURRENT_TIMESTAMP

VARIABLES 

TIME 

Returns the current timestamp of the operating system of the server on which the SQL Server Database runs
DATE

DATA_MANIPULATING 

CONVERTING 

A data type for storing a date (string like 'YYYYMMDD')
DATE_ADD

MATH 

TIME 

Adds time values (as intervals) to a date value
DATE_SUB

MATH 

TIME 

Subtracts a time/date interval from a date and then returns the date
DATEADD

MATH 

TIME

Adds time values (as intervals) to a date value
DATEDIFF

MATH 

TIME 

Returns the difference between two dates
DAY

DATA_MANIPULATING 

TIME 

CONVERTING 

Returns the day of the month for a date
DAYOFWEEK

DATA_MANIPULATING 

TIME 

Returns the weekday index for a date


DEFAULT

DATA_MANIPULATING 

LOGIC 

Provides a default value for a column
DESC

MATH 

ORDERING 

Descending sorting order
DISTINCT

LOGIC 

AGGREGATION 

Selects only distinct (different) values
ELSE

LOGIC

Introduces another statement that is executed when the IF condition is not satisfied
END

LOGIC

Completes code block for WHILE and IF statements
ESCAPE

LOGIC 

SEARCHING 

Equals to LIKE
ESCAPEMARKUP

STRING_OPERATORS

Escapes special characters for wiki markup
EXCEPT

MERGING

Combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement
EXISTS

LOGIC

Tests for the existence of any record in a subquery
EXP

MATH

Returns e raised to the power of the specified number
FALSE

LOGIC

Logical false value
FIRST

AGGREGATION

Returns the first value in the group
FLOOR

MATH

Returns the largest integer value that is smaller than or equal to a number
FORMATDATE

DATA_MANIPULATING 

TIME 

Converts dates to the specified in the Table Transformer macro settings date format: FORMATDATE (calculated_date, date_format_to_specify <- optional argument)
FORMATMARKDOWN

FORMATTING

Applies Markdown syntax to table cells
FORMATNUMBER

DATA_MANIPULATING 

FORMATTING

Format numbers according to the settings in the Table Transformer macro: FORMATNUMBER (calculated_number, decimal_separator, thousands_separator, decimal_places <- optional arguments used simultaneously)
FORMATTIME

DATA_MANIPULATING 

TIME 

Converts time to the specified in the Table Transformer macro settings time format to the calculated time: FORMATTIME (calculated_time)
FORMATWIKI

FORMATTING

Applies Confluence Wiki Markup to table cells
FORMATWORKLOG

DATA_MANIPULATING 

TIME 

Converts worklogs to the specified in the Table Transformer macro worklog settings: FORMATWORKLOG (calculated_worklog)
FROM

KEYWORD

Specifies which table to select or delete data from
FULL

MERGING

FULL OUTER JOIN: returns all rows when there is a match in either left table or right table
GETDATE

DATA_MANIPULATING 

TIME

Returns the current system date and time
GREATEST

MATH 

AGGREGATION 

Returns the greatest value of the list of arguments
GROUP

AGGREGATION

Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
GROUPING

LOGIC 

AGGREGATION 

Indicates whether the specified column in a GROUP BY Clause aggregated or not
HAVING

LOGIC 

AGGREGATION 

Used instead of WHERE with aggregate functions
HOUR

DATA_MANIPULATING 

TIME 

Returns the hour part of a datetime
IF

LOGIC

Conditional statement
IFNULL

LOGIC

Returns a specified value if the expression is NULL
IIF

LOGIC

Accepts three arguments. It evaluates the first argument and returns the second argument if the first argument is true; otherwise, it returns the second argument
IN

LOGIC

Allows you to specify multiple values in a WHERE clause

INNER

MERGING

INNER JOIN: returns rows that have matching values in both tables
INSERT

DATA_MANIPULATING

Inserts new records into a table (see an example)
INSTR

STRING_OPERATORS

Returns the position of the first occurrence of a string in another string
INTERSECT

MERGING

Intersects two SELECT results
INTERVAL

TIME

Defines a date/time interval
IS

LOGIC


ISNULL

LOGIC

Returns TRUE or FALSE depending on whether an expression is NULL

JOIN

MERGING

Joins tables (see an example)
LAST

DATA_MANIPULATING

Returns the last value of the selected column
LCASE

STRING_OPERATORS

Converts a string to lower-case
LEAST

MATH 

AGGREGATION 

Returns the smallest value of the list of arguments
LEFT

MERGING

LEFT JOIN: returns all rows from the left table, and the matching rows from the right table
LENGTH

STRING_OPERATORS

Returns the length of a string (in bytes)
LIKE

LOGIC 

SEARCHING 

Searches for a specified pattern in a column
LIMIT

SEARCHING

Limit the number of records from the result set
LOG

MATH

Returns the natural logarithm
MATCH_REGEXP

DATA_MANIPULATING 

LOGIC

Allows to add flags (i.e. g (global), i (insensitive), etc.) to regular expressions: MATCH_REGEXP ('Column1',"[a-f]{6}","g")
MAX

MATH 

AGGREGATION 

Takes the name of a column as an argument and returns the largest value in that column
MEDIAN

MATH 

AGGREGATION 

Returns the median value of a group (see an example)
MID

STRING_OPERATORS

Extracts a substring from a string (starting at any position)
MIN

MATH 

AGGREGATION 

Takes the name of a column as an argument and returns the smallest value in that column
MINUTE

DATA_MANIPULATING 

TIME 

Returns the minute part of a datetime value
MONTH

DATA_MANIPULATING 

TIME 

Returns the month part of a date
MULT

MATH 

AGGREGATION

Takes the name of a column as an argument and returns multiplying of all the values in that column

NATURAL

MERGING

NATURAL JOIN: analyzes columns in two joined tables and makes a join by columns with similar names
NOT

LOGIC

Includes rows where a condition is not TRUE
NOW

VARIABLES

Returns current date and time
NULL

LOGIC

A field with no value
NULLIF

LOGIC

Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned
OF

LOGIC

Allows you to specify multiple values in a WHERE clause
OFFSET

SEARCHING

Skips the first N rows in a result set before starting to return any rows
ON

MERGING


OR

LOGIC

Includes rows where either condition is TRUE
ORDER

ORDERING

Sorts the result set in ascending or descending order
OUT

LOGIC

Opposite to IN
OUTER

MERGING

OUTER JOIN: returns all rows when there is a match in either left table or right table
PERCENT

SEARCHING

Indicates that the query returns N percentage of rows
PIVOT

AGGREGATION

Converts data from row level to column level

POWER

MATH

Returns the value of a number raised to the power of another number
QUART

MATH 

AGGREGATION

Returns the first quartile of a group
QUART2

MATH 

AGGREGATION

Returns the second quartile of a group
QUART3

MATH 

AGGREGATION

Returns the third quartile of a group
RANDOM

VARIABLES


REGEXP_LIKE

LOGIC 

STRING_OPERATORS 

SEARCHING 

Returns rows that match a regular expression pattern
REPLACE

STRING_OPERATORS

Replaces all occurrences of a substring within a string, with a new substring
REPLACE_VIEW

STRING_OPERATORS

Replaces all occurrences of a substring within a string, with a new substring, preserving its original HTML format
RIGHT

MERGING

RIGHT JOIN: returns all rows from the right table, and the matching rows from the left table
ROLLUP

MATH 

AGGREGATION 

Creates subtotals and grand totals for a set of columns (GROUP BY ROLLUP)
ROUND

MATH

Rounds a number to a specified number of decimal places
ROWNUM

DATA_MANIPULATING

Adds row numbering to a table
SEARCH

DATA_MANIPULATING

Searches for a specified pattern (see an example)
SECOND

DATA_MANIPULATING 

TIME 

Return the seconds part of a datetime value
SELECT

DATA_MANIPULATING

Selects data from a database
SOME

LOGIC

Compares a value to each value in a list or results from a query and evaluates to TRUE if the result of an inner query contains at least one row
SQRT

MATH

Returns the square root of a number
STDDEV

MATH 

AGGREGATION 

Returns the population standard deviation of expression (see an example)
STDEV

MATH 

AGGREGATION 

Returns the sample standard deviation of expression (see an example)
SUBDATE

MATH 

TIME 

Subtracts a time/date interval from a date and then returns the date
SUBSTR

STRING_OPERATORS

Extracts a substring from a string (starting at any position)
SUBSTRING

STRING_OPERATORS

Allows to extract a substring from a string
SUBSTRING_VIEW

STRING_OPERATORS

Allows to extract a substring from a string, preserving its original HTML format
SUM

MATH 

AGGREGATION 

Takes the name of a column as an argument and returns the sum of all the values in that column (see an example)
THEN

LOGIC

A result expression in a search condition
TIME

DATA_MANIPULATING 

TIME 

Extracts the time part from a given time/datetime

CONVERTING

Returns a datetime value based on a date or datetime value

TO

KEYWORD


STRING_OPERATORS

Removes the space character or other specified characters from the start or end of a string
TRUE

LOGIC

Logical true value
UCASE

STRING_OPERATORS

Converts a string to upper-case
UNION

MERGING

Combines the result set of two or more SELECT statements (only distinct values)
UPDATE

DATA_MANIPULATING

Modifies the existing records in a table (see an example)
UPPER

STRING_OPERATORS

Converts a string to upper-case
USING

LOGIC

Matches only one column when more than one column matches
VAR

MATH 

AGGREGATION 

Returns the statistical variance of all values in the specified expression
VAR_POP

AGGREGATION

Population variance. Returns 0 if all of the values in the data set have the same value (no variability). Returns 0 if the data set consists of only one value (no possible variability). Returns NULL if the data set has no values
VAR_SAMP

AGGREGATION

Sample variance. Returns 0 if all of the values in the data set have the same value (no variability). Returns NULL if the data set consists of only one value (no possible variability). Returns NULL if the data set has no values
VARCHAR

KEYWORD

Accepts character strings of a variable length is up to the maximum length specified in the data type declaration
VARP

AGGREGATION

Returns the Variance for the population of the total number of records present in the specified column
WEEKDAY

DATA_MANIPULATING 

TIME 

Returns the weekday number for a given date
WHEN

LOGIC

A condition expression in a search condition
WHERE

LOGIC 

SEARCHING 

Filters a result set to include only records that fulfill a specified condition
WHILE

LOOP

Sets a condition for the repeated execution of an SQL statement or statement block
YEAR

DATA_MANIPULATING 

TIME 

Returns an integer value which represents the year of the specified date
TIMESTAMPDIFF

MATH 

TIME 

Returns the difference between two dates
TEXT

STRING_OPERATORS

Returns the source string without additional formatting
HTML_DECODE

STRING_OPERATORS

Converts the displayed string into HTML markup and extracts text content from it

Conversion types

  • ::Date
  • ::string
  • ::number

For date conversion see also the FORMATDATE, FORMATTIME, FORMATWORKLOG functions.

JavaScript methods

After converting to date you can use JavaScript date functions, for example:

'Column with dates'::Date→toDateString()

Find an example here.

After converting to string you can use JavaScript string functions, for example:

'Column with strings'::string->split("The delimiter")->0

'Column with strings'::string→length

After converting to number you can use JavaScript number functions, for example:

'Column with numbers'::number→toFixed(1)


You need to convert only values with a different or indefinite type. In other cases, it's is not necessary:

'Column with numbers'->toFixed(1)

Example

The source table:

The result

SQL query:

SELECT 
T1.'String'->length AS 'Length',
T1.'String'->split("b")->0 AS 'Splitting',
T1.'Number'->toFixed(1) AS 'Rounding to tenth',
T1.'Date'::Date->getFullYear() AS 'Full year'
FROM T*