Supported Operators

The table below contains all the VTL 2.0 operators with their VtlProcessing implementation status.

Operators marked as VtlProcessing.Core are supported by this part of library. It means that the operator will be recognized by the front and middle-end parts of the translator and proper intermediate representation will be generated. If an operators is marked as VtlProcessing.Target.TSQL then it can be translated to T-SQL target code.

Operator Symbol Obsługa VtlProcessing.Core Obsługa VtlProcessing.Target.TSQL

Parentheses

()

Persistent assignment

<-

Non-persistent assignment

:=

Membership

#

User-definied operator call

Evaluation of an external routine

eval

Type conversion

cast

Join

inner_join, left_join, full_join, cross_join

String concatenation

||

Whitespace removal

trim, rtrim, ltrim

Character case conversion

upper/lower

Sub-string extraction

substr

String pattern replacement

replace

String pattern location

instr

String length

length

Unary plus

+

Unary minus

-

Addition

+

Substraction

-

Multiplication

*

Division

/

Modulo

mod

Rounding

round

Truncation

trunc

Ceiling

ceil

Floor

floor

Absolute value

abs

Exponential

exp

Natural logarithm

ln

Power

power

Logarithm

log

Square root

sqrt

Equal to

=

Not equal to

<>

Greater than

> >=

Less than

< <=

Between

between

Element of

in/not_in

match_characters

match_characters

isnull

isnull

Exists in

exists_in

Logical conjunction

and

Logical disjunction

or

Exclusive disjunction

xor

Logical negation

not

Period indicator

period_indicator

Fill time series

fill_time_series

Flow to stock

flow_to_stock

Stock to flow

stock_to_flow

Time shift

timeshift

Time aggregation

time_agg

Actual time

current_date

Union

union

Intersection

intersect

Set difference

setdiff

Simmetric difference

symdiff

Hierarchical roll-up

hierarchy

Aggregate invocation

Analytic invocation

Counting the number of data points

count

Minimum value

min

Maximum value

max

Median value

median

Sum

sum

Average value

avg

Population standard deviation

stddev_pop

Sample standard deviation

stddev_samp

Population variance

var_pop

Sample variance

var_samp

First value

first_value

Last value

last_value

Lag

lag

Lead

lead

Rank

rank

Ratio to report

ratio_to_report

check_datapoint

check_datapoint

check_hierarchy

check_hierarchy

check

check

if-then-else

if

Nvl

nvl

Filtering Data Points

filter

Calculation of a Component

calc

Aggregation

aggr

Maintaining Components

keep

Removal of Components

drop

Change of Component name

rename

Pivoting

pivot

Unpivoting

unpivot

Subspace

sub

Time Value Domains Support

Time Data Type Masks

Proper support for VTL time data types is limited to masks described in the table below. When using a time data type in VTL code or storing data on time value domain field, you need to respect these masks. Otherwise, translation will not be correct.

Time data type Supported masks

Date

"yyyy-mm-dd",
"yyyy-mm"

Time

"yyyy-mm-dd/yyyy-mm-dd",
"yyyy-mm/yyyy-mm"

TimePeriod

"yyyy",
"yyyyA"
"yyyyS{s}"
"yyyyQ{q}"
"yyyyM{mm}"
"yyyyW{ww}"
"yyyyD{ddd}"

Duration

"A", "S", "Q", "M", "W", "D"

Simplified Usage of Time Data Types

Originally, scalar time data type literal required a usage of the type conversion operator (cast). String literal can be converted to a time value, respecting proper mask.

Example of using the CAST operator to acquire scalar time value
cast("2000Q1", time_period, "YYYY\QQ")
cast("20120213", date, "YYYYMMDD")

In order to simplify this task, VTL Processing provides functionality to automatically recognize time literals without using cast operator. The masked string need to be preceded by letter t.

Example of simplified literals for time data types
t"2000Q1"
t"2012-02-13"
Functionality works properly only for masks from the table above.