Skip to Content

mvSQL in Jitterbit App Builder

Overview

mvSQL is App Builder's own dialect of SQL. It allows users to learn and use a single set of syntax and functions, while App Builder translates that syntax to vendor appropriate expressions. mvSQL can be used to query relational database, storage, REST, file systems, or any other kind of data provider App Builder supports.

mvSQL versions

mvSQL versions supported by App Builder are listed with the oldest supported version appearing first (Version 1).

Version 1

The oldest legacy version App Builder supports, Version 1 mvSQL is even stricter than we allowed before refactoring the grammar and parser. Most legacy App Builder apps should run using this version.

Version 2

Version 2 mvSQL improves on passthrough syntax, supports the ability to close it and resume regular mvSQL expressions, and supports escaping characters.

Example

Version 1 would see this (in MS SQL Server) ${ [vendor syntax] } || 'my example' as ${ [vendor syntax] || 'my example' } rendering [vendor syntax] || 'my example'.

Version 2 would parse as it was meant to be, and render as [vendor syntax] + 'my example'. Note the concat being vendor appropriate).

Version 3

Version 3 mvSQL requires table prefixes to be correct. Up to this version, if the table prefix was incorrect, and App Builder could infer it by checking the other columns, we would allow incorrect table prefixes.

Example

In previous versions of mvSQL, using a table alias that doesn't exist, or even one that does but that doesn't have the referenced column, would still work as long as that column name only showed up in one of the sources. This scenario presents an issue when that same column name is added to another source and the business rule stops working.

Version 4

In version 4 mvSQL, Runtime functions output a proper type, instead of always being a string, and null values are not coalesced into an empty string.

Example

Shared(ColumnName, numeric)

  • Prior to version 4, App Builder would either output '0' if the column wasn't provided, or '1' if it was (note that is rendered as a string, assuming the shared value here is 1 of course)
  • Beginning in version 4, App Builder now outputs null if it wasn't provided, and 1 (without the quotes), we also parametrized if the vendor supports it (@p0), as this can leverage better performance from the vendor.
  • This also means it's easier to coalesce the value, before if you wanted to change the value if it wasn't provided you would need something like IIF(Shared(ColumnName) = '', -1, Shared(ColumnName, numeric)), now you can use ISNULL(Shared(ColumnName, numeric), -1)
  • New rules are created using the highest available version (version 4), but to avoid breaking legacy rules, we kept them as they were. If changes are made to a rule that won't be affected by this (mainly rules without runtime functions) App Builder will auto update the mvSQL version.

Version 5

In version 5, mvSQL treats any passthrough as GROUP-able when part of an aggregate query.

To indicate that an expression is an aggregate, call it with the Expression() function.

Example

Expression(${Count(1)})

Resources