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

1<input data>.asSQLGrouping(SQLType, includeKeywords)

Arguments

Argument	Description
`SQLType`	(Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`.
`includeKeywords`	(Optional) The default value is `false`.

For SOQL, use sobject.

Note

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.

1[
2    {grouping: "first", alias: "foo"}
3    {grouping: "second", alias: "bar"}
4]

Examples

Function:

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

Output in standard or Snowflake live dataset:

1GROUP BY "first"

Function:

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

Output in SOQL:

1GROUP BY first

Function:

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

Output in standard or Snowflake live dataset:

1"first"

Function:

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

Output in SOQL:

1first

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

Function:

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

Output in standard or Snowflake live dataset:

1GROUP BY "foo", "bar"

Function:

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

Output in SOQL:

1GROUP BY foo, bar

Function:

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

Output in standard or Snowflake live dataset:

1"foo", "bar"

Function:

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

Output in SOQL:

1foo, 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

1<input data>.asSQLHaving(SQLType, includeKeywords)

Arguments

Argument	Description
`SQLType`	(Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`.
`includeKeywords`	(Optional) The default value is `false`.

For SOQL, use sobject.

Note

These examples are based on the stepFoo source query.

The < and >=<= operators aren’t supported.

Note

Examples

Function:

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

Output in standard or Snowflake live dataset:

1HAVING "SUM(Revenue)" > 10

Function:

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

Output in SOQL:

1HAVING SUM(Revenue) > 10

Function:

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

Output in standard or Snowflake live dataset:

1"SUM(Revenue)" > 10

Output in SOQL:

1SUM(Revenue) > 10

Function:

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

Output in standard, Snowflake live dataset, or SOQL:

1""

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

1<input data>.asSQLOrder(SQLType, includeKeywords)

Arguments

Argument	Description
`SQLType`	(Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`.
`includeKeywords`	(Optional) The default value is `false`.

For SOQL, use sobject.

Note

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

1values: [
2    {order: "first", direction: "desc"}
3    {order: "second", direction: "asc"}
4]

Examples

Function:

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

Output in standard or Snowflake live dataset:

1ORDER BY "first"

Function:

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

Output in SOQL:

1ORDER BY first

Function:

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

Output in standard or Snowflake live dataset:

1"first", "second"

Function:

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

Output in SOQL:

1first, second

Function:

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

Output in standard or Snowflake live dataset:

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

Function:

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

Output in SOQL:

1ORDER 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

1<input data>.asSQLSelect(SQLType)

Arguments

Argument	Description
`SQLType`	(Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`.

For SOQL, use sobject.

Note

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

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

Examples

Function:

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

Output in standard or Snowflake live dataset:

1SUM("first") AS "foo"

Function:

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

Output in SOQL:

1SUM("first") foo

Function:

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

Output in standard or Snowflake live dataset:

1SUM("first")

Function:

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

Output in SOQL:

1SUM("first")

Function:

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

Output in standard or Snowflake live dataset:

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

Function:

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

Output in SOQL:

1SUM("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

1<input data>.asSQLWhere(SQLType, includeKeywords)

Arguments

Argument	Description
`SQLType`	(Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`.
`includeKeywords`	(Optional) The default is false.

For SOQL, use sobject.

Note

These examples are based on the stepFoo source query.

Examples

Function:

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

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

1""

Function:

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

Output in standard or Snowflake live dataset:

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

Function:

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

Output in SOQL:

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

Function:

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

Output in standard or Snowflake live dataset:

1"bar" > 10 AND "bar" < 100

Function:

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

Output in SOQL:

1bar > 10 ad bar < 100

Multiple Filters

Function:

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

Output in standard or Snowflake live dataset:

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

Function:

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

Output in SOQL:

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

Function:

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

Output in standard dataset:

1WHERE ("CreatedDate"  >= DATE '2002-3-19' AND "CreatedDate" &lt;= '2010-8-12'))

Function:

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

Output in Snowflake live dataset:

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

Function:

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

Output in SOQL:

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

Function:

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

Output in standard dataset:

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

Function:

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

Output in Snowflake live dataset:

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

Function:

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

Output in SOQL:

1CreatedDate <= 2010-8-12

Function:

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

Output in standard dataset:

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

Function:

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

Output in Snowflake live dataset:

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

Function:

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

Output in SOQL:

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

Function:

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

Output in Snowflake live dataset:

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

Function:

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

Output in SOQL:

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