Data Serialization Functions for SQL and SOQL

SQL and SOQL serialization functions convert the data into the form expected by the query in which the interaction is inserted.

asSQLGrouping Function

Returns a grouping as a string in a SQL or SOQL query. Can also return multiple groupings.

The input data must be a scalar or a one-dimensional array of groupings. Null or no results returns an ungrouped query.

Syntax

<input data>.asSQLGrouping(SQLType, includeKeywords)

Arguments

note: For SOQL, use sobject.

Let's look at some examples where the selection detemines the groupings in a SQL or SOQL-form query. These examples are based on the stepFoo source query. Assume that stepFoo.selection retrieves these rows from the query.

[
    {grouping: "first", alias: "foo"}
    {grouping: "second", alias: "bar"}
]

Examples

Function:

(cell(stepFoo.selection, 0, "grouping").asSQLGrouping("snowflake", true))

Output in standard or Snowflake live dataset:

GROUP BY "first"

Function:

(cell(stepFoo.selection, 0, "grouping").asSQLGrouping("sboject", true))

Output in SOQL:

GROUP BY first

Function:

(cell(stepFoo.selection, 0, "grouping").asSQLGrouping("snowflake", false))

Output in standard or Snowflake live dataset:

"first"

Function:

(cell(stepFoo.selection, 0, "grouping").asSQLGrouping("sboject", false))

Output in SOQL:

first

To make the interaction return multiple fields for the grouping, replace the cell interaction function with a column function and update the arguments.

Function:

(column(stepFoo.selection, ["alias"]).asSQLGrouping("snowflake", true))

Output in standard or Snowflake live dataset:

GROUP BY "foo", "bar"

Function:

(column(stepFoo.selection, ["alias"]).asSQLGrouping("sobject", true))

Output in SOQL:

GROUP BY foo, bar

Function:

(column(stepFoo.selection, ["alias"]).asSQLGrouping("snowflake", false))

Output in standard or Snowflake live dataset:

"foo", "bar"

Function:

(column(stepFoo.selection, ["alias"]).asSQLGrouping("sobject", false))

Output in SOQL:

foo, bar

asSQLHaving Function

Returns the grouped results for a SQL or SOQL query restricted by a conditional filter logic applied on aggregate functions. This function is similar to the asSQLWHERE function.

Syntax

<input data>.asSQLHaving(SQLType, includeKeywords)

Arguments

note: For SOQL, use sobject.

These examples are based on the stepFoo source query.

note: The < and >=<= operators aren't supported.

Examples

Function:

(toArray("SUM(Revenue)", ">",column(stepFoo.selection, ["Revenue"]).asSQLHaving("snowflake", true))

Output in standard or Snowflake live dataset:

HAVING "SUM(Revenue)" > 10

Function:

(toArray("SUM(Revenue)", ">",column(stepFoo.selection, ["Revenue"]).asSQLHaving("sobject", true))

Output in SOQL:

HAVING SUM(Revenue) > 10

Function:

(toArray("SUM(Revenue)", ">",column(stepFoo.selection, ["Revenue"]).asSQLHaving("dataset"))

Output in standard or Snowflake live dataset:

"SUM(Revenue)" > 10

Output in SOQL:

SUM(Revenue) > 10

Function:

([].asSQLHaving('dataset')) or ([].asSQLHaving('dataset', 'false'))

Output in standard, Snowflake live dataset, or SOQL:

""

asSQLOrder Function

Returns the sort order as a string for a SQL or SOQL query.

The input data must be scalar, one-dimensional, or a two-dimensional array. A two-dimensional array is treated as a tuple of interactions.

Syntax

<input data>.asSQLOrder(SQLType, includeKeywords)

Arguments

note: For SOQL, use sobject.

These examples are based on the stepFoo source query. Assume that stepFoo.selection retrieves these rows from the query.

values: [
    {order: "first", direction: "desc"}
    {order: "second", direction: "asc"}
]

Examples

Function:

(cell(stepFoo.selection, 0, "order").asSQLOrder("snowflake", true))

Output in standard or Snowflake live dataset:

ORDER BY "first"

Function:

(cell(stepFoo.selection, 0, "order").asSQLOrder("sboject", true))

Output in SOQL:

ORDER BY first

Function:

(cell(stepFoo.selection,["order")].asSQLOrder("snowflake"))

Output in standard or Snowflake live dataset:

"first", "second"

Function:

(cell(stepFoo.selection, ["order"]).asSQLOrder("sboject"))

Output in SOQL:

first, second

Function:

(row(stepFoo.selection, [], ["order", "direction"]).asSQLOrder("snowflake", true))

Output in standard or Snowflake live dataset:

ORDER BY "first" DESC, "second", ASC

Function:

(row(stepFoo.selection, [], ["order", "direction"]).asSQLOrder("sobject", true))

Output in SOQL:

ORDER BY first DESC, second ASC

asSQLSelect Function

Returns the SQL or SOQL expression and alias as a string that you can use to project a field in the query. The query expression determines the value of the field. The alias is the field label.

Syntax

<input data>.asSQLSelect(SQLType)

Arguments

note: For SOQL, use sobject.

These examples are based on the stepFoo source query. Assume that stepFoo.selection retrieves these rows from the query.

values: [
    {expression: "SUM("first")", alias: "foo"}
    {expression: "second", alias: "bar"}
]

Examples

Function:

(row(stepFoo.selection, [0], ["expression", "alias"]).asSQLSelect("snowflake"))

Output in standard or Snowflake live dataset:

SUM("first") AS "foo"

Function:

(row(stepFoo.selection, [0], ["expression", "alias"]).asSQLSelect("sobject"))

Output in SOQL:

SUM("first") foo

Function:

(row(stepFoo.selection, [0], ["expression"]).asSQLSelect("snowflake"))

Output in standard or Snowflake live dataset:

SUM("first")

Function:

(row(stepFoo.selection, [0], ["expression"]).asSQLSelect("sobject"))

Output in SOQL:

SUM("first")

Function:

(row(stepFoo.selection, [], ["expression", "alias"]).asSQLSelect("snowflake"))

Output in standard or Snowflake live dataset:

SUM("first") AS "foo", "second" AS "bar"

Function:

(row(stepFoo.selection, [], ["expression", "alias"]).asSQLSelect("sobject"))

Output in SOQL:

SUM("first") foo, second bar

asSQLWhere Function

Returns the SQL or SOQL query results restricted by a conditional filter logic applied on a measure, dimension, or date.

Syntax

<input data>.asSQLWhere(SQLType, includeKeywords)

Arguments

note: For SOQL, use sobject.

These examples are based on the stepFoo source query.

Examples

Function:

([].asSQLWhere('dataset')) or ([].asSQLWhere('dataset', 'false'))

Output in standard dataset, Snowflake live dataset, or SOQL:

""

Function:

((toArray("foo", "IN", column(stepFoo.selection, ["foo"])).asSQLWhere("snowflake", true))
toArray("foo", "IN", column(stepFoo.selection, ["foo"])  =>  ["foo", "IN", ["a", "b"]]

Output in standard or Snowflake live dataset:

WHERE "foo" in ('a', 'b')

Function:

((toArray("foo", "IN", column(stepFoo.selection, ["foo"])).asSQLWhere("sobject", true))
toArray("foo", "IN", column(stepFoo.selection, ["foo"])  =>  ["foo", "IN", ["a", "b"]]

Output in SOQL:

WHERE foo in ('a', 'b')

Function:

((toArray("bar", "><", column(stepFoo.selection, ["bar"])).asSQLWhere("snowflake", true))
toArray("bar", "><", column(stepFoo.selection, ["bar"])  =>  ["bar", "><", [10, 100]]

Output in standard or Snowflake live dataset:

"bar" > 10 AND "bar" < 100

Function:

((toArray("bar", "><", column(stepFoo.selection, ["bar"])).asSQLWhere("sobject", true))
toArray("bar", "><", column(stepFoo.selection, ["bar"])  =>  ["bar", "><", [10, 100]]

Output in SOQL:

bar > 10 ad bar < 100

Multiple Filters

Function:

(toArray(toArray("foo", "IN", column(stepFoo.selection, ["foo"]),
toArray("bar", "><;", column(stepFoo.selection, ["bar"]))).asSQLWhere("snowflake", true))

Output in standard or Snowflake live dataset:

WHERE "foo" IN ('a', 'b') AND ("bar" > 10 AND "bar" < 100)

Function:

(toArray(toArray("foo", "IN", column(stepFoo.selection, ["foo"]),
toArray("bar", "><;", column(stepFoo.selection, ["bar"]))).asSQLWhere("sobject", true))

Output in SOQL:

WHERE foo IN ('a', 'b') AND (bar > 10 AND bar < 100)

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("dataset", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [1016504910000, 1281655993000]]

Output in standard dataset:

WHERE ("CreatedDate"  >= DATE '2002-3-19' AND "CreatedDate" <= '2010-8-12'))

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("snowflake", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [1016504910000, 1281655993000]]

Output in Snowflake live dataset:

WHERE ("CreatedDate"  BETWEEN to_date('2002-3-19') AND to_date('2010-8-12'))

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("sobject", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [1016504910000, 1281655993000]]

Output in SOQL:

WHERE (CreatedDate  >= DATE 2002-3-19 AND CreatedDate <= 2010-8-12))

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("dataset"))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [null, 1281655993000]]

Output in standard dataset:

"CreatedDate" <= DATE '2010-8-12'

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("snowflake"))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [null, 1281655993000]]

Output in Snowflake live dataset:

"CreatedDate" <= to_date('2010-8-12')

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("sobject"))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [null, 1281655993000]]

Output in SOQL:

CreatedDate <= 2010-8-12

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("dataset", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [2015, 8, 1], [2021, 3, 1]]

Output in standard dataset:

WHERE ("CreatedDate" >= DATE '2015-8-1' AND CreatedDate <= '2021-3-1'))

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("snowflake", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [2015, 8, 1], [2021, 3, 1]]

Output in Snowflake live dataset:

WHERE ("CreatedDate" BETWEEN to_date('2015-8-1') AND to_date('2021-3-1'))

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("sobject", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [2015, 8, 1], [2021, 3, 1]]

Output in SOQL:

WHERE (CreatedDate >= 2015-8-1 AND CreatedDate <= 2021-3-1))

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("snowflake", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", ["quarter", -2], ["quarter", 3]

Output in Snowflake live dataset:

WHERE ("CreatedDate" >= date_trunc(quarter, dateadd(quarter, -2, current_date()))AND "CreatedDate" <>= date_trunc(quarter, dateadd(quarter, 3, current_date())))

Function:

(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("sobject", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", ["quarter", -2], ["quarter", 3]

Output in SOQL:

WHERE (CreatedDate >= LAST_N_QUARTERS:2 AND CreatedDate <>= NEXT_N_QUARTERS:3)