whereClause (myView)

The whereClause in the GET.myView function specifies the filter criteria to apply to the DataMart.

The syntax of a whereClause is similar to an SQL WHERE clause.

Key Details:

• The whereClause must be passed as a text string encapsulated in single quotes (' ').
• It supports the following:
  • Keywords: AND, OR, NOT
  • Comparison operators: =, <=, <, <>, >, >=, LIKE
  • The LIKE operation supports the % wildcard.
  • Set operations: IN() and NOT IN()

Additional Rules:

• Field constants must be specified as shown in the column headings (e.g., LOAD_DATE).
• The property value in a comparison statement requires single quotation marks ('). If these are used within the whereClause, they must be escaped by doubling them.
• Date constants should be formatted as 'yyyy-mm-dd'.

Example of a whereClause:

= Sql.Database(DataMart_Host, DataMart_Endpoint, [Query="EXEC get.myview @dataMart = 'resource', @viewName = 'activity', @token = '"& DataMart_Token &"', @version = '202404', @whereClause = 'LOAD_DATE > ''2024-04-01'''"])

In this example, the LOAD_DATE > '2024-04-01' comparison uses double single quotation marks ('') to escape the inner quotes.