KB485720: Dossier transaction gateway usage suggestions and workarounds for known defects/limitations

Product Owner, Senior •

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Introduction

In Strategy 2021 Update 6, dossier users with the proper privileges can modify data and submit data changes to the database. The dossier transaction configuration automatically generates the basic INSERT, UPDATE, and DELETE SQL statements with tokens according to the mapping view configuration. Customized SQL statements with the correct syntax can be used to complete the transaction configuration.

Certified Gateways:

SQL Server
Oracle
PostgreSQL
MySQL
SAP HANA
Google BigQuery
Snowflake

Recommendations

Consider the following recommendations when designing your transaction configuration.

Only relational database transactions are supported

Strategy supports connectivity to multiple Hadoop components and SaaS applications through an ODBC or JDBC driver, such as Hive, Salesforce, or MongoDB. These drivers support data retrieval (queries) but do not support data manipulation.

In gateways based in read-only files, like the Hadoop components Hive, Impala, SparkSQL, or MongoDB, the use case of transactions may not be applicable. Further configuration to enable this is not in the scope of this document.
For SaaS gateways, like Salesforce, consider using traditional Strategy document and transaction reports to use XQuery and submit SOAP or REST calls to update data. See Defining Input-Dependent SQL or XQuery Syntax and Using XQuery to Retrieve Data from a Web Service for more information.
For SAP HANA analytical views that may have assigned input parameters, update operations are not allowed by SAP HANA.

Troubleshoot SQL statements submitted to your database

To troubleshoot SQL statements submitted to the source, use the diagnostic trace Engine > SQL Trace. See Configuring What is Logged for more information.

If the database namespace, schema, table, or column set in the transaction configuration uses special characters, consider adding quotes

Most databases accept double quotes as table and column identifiers. The SQL statement generated by the dossier transaction configuration may not include them. To escape special characters, use the specifications that correspond to your gateway. Use the grid further below as reference.

To set the WHERE clause for UPDATE/DELETE configurations, ensure the selected columns uniquely identify a set of rows

Avoid selecting columns that may lead multiple rows being updated. You may rely on a table primary key or unique identifier. Do not use decimal, double, or float data types in the WHERE clause, as data precision may cause issues with matching the rows correctly.

In Input Configuration, ensure the data type of the column matches

In Strategy 2021 Update 6, by default, the controls in the Input Configuration handle inputs as "VarChar" (text) for attributes and "Integer" for metrics.

In Strategy 2021 Update 7, in the configuration mapping view, the data type for each control is populated according to the target database source. In the configuration SQL view, the data type for each control is populated according to the mapped template object.

Review and update the control with the right data type and use the right data format to allow Strategy to update values in the format expected by the source. This can be problematic with date and date-time columns where a date must follow a specific format or even explicit data type cast to be accepted by the source.

Example 1
Your input has a data type of "Date", "Date Time", and "Time" to a MySQL database. You want to add a record with the values "12/31/2022", "23:59:59.001", and "12/31/2022 23:59:59.001". These are values accepted by Strategy as date, time, and date time format.

Leaving the default option for updating the Input Control data type, the transaction configuration concatenates the input and submits the following, which is rejected by the MySQL database server.
```
INSERT INTO database_namespace.database_table (DATE_type_column, DATETIME_type_column, TIME_type_column) VALUES ('12/31/2022','12/31/2022 23:59:59.001','23:59:59.001')
```
Update to the correct data type. The Strategy engine validates the data type and submits the type expected to the source. For the same MySQL table, the submitted SQL statement becomes the following which is accepted by the MySQL database server.
```
INSERT INTO database_namespace.database_table (DATE_type_column, DATETIME_type_column, TIME_type_column) VALUES ('2022-12-31','2022-12-31 23:59:59','23:59:59')
```

Note: Default formats for Strategy are set at VLDB Data Type settings and can be adjusted at the DBMS, database instance, and report level.

Date Format = "mm/dd/yyyy" (May also accept "yyyy-mm-dd”).
Time Format = "hh:nn:ss"
TimeStamp Format = "yyyy-mm-dd hh:nn:ss"

Example 2
Your input has a data type of "Date", "Date Time", and "Time" to a MySQL database. Your type values of "31-12-2022", "11:59:59.001 PM", "31-12-2022 11:59:59.001 PM" are not accepted by the Strategy engine. You set the correct data type on the Input Configuration. Your transaction fails with an error.


[Dimensional Storage][Error][CDSSTransactionData.cpp:2649] You have input an invalid value '31-12-2022 11:59:59.001 PM', acceptable data type is 'TimeStamp'.

[Dimensional Storage][Error][CDSSTransactionData.cpp:2649] You have input an invalid value '11:59:59.001 PM', acceptable data type is 'Time'.

Example 3
You expect users to enter "Date", "Time", "Date Time" in a different format than Strategy: "31-12-2022", "11:59:59.001 PM", "31-12-2022 11:59:59.001 PM". You set the transaction grid to capture date time strings in a format recognized by the user, by using functions available in the database (in this case, SQL_TO_DATE()).

The transaction is updated by the user into


INSERT INTO database_namespace.database_table ( {DATE_type_column}, {DATETIME_type_column}, {TIME_type_column} ) VALUES (STR_TO_DATE ([Date Type@ID], '%d-%m-%Y'), STR_TO_DATE ( [Datetime Type@ID], '%d-%m-%Y %h:%i:%s.%f %p'), STR_TO_DATE ([Time Type@ID], '%h:%i:%s.%f %p'))

The following SQL statement is submitted and accepted by the MySQL database.


INSERT INTO database_namespace.database_table (DATE_type_column, DATETIME_type_column, TIME_type_column) VALUES ( STR_TO_DATE ('31-12-2022', '%d-%m-%Y'), STR_TO_DATE ( '31-12-2022 11:59:59.001 PM', '%d-%m-%Y %h:%i:%s.%f %p' ), STR_TO_DATE ('11:59:59.001 PM', '%h:%i:%s.%f %p') )

Refer to your database documentation for the supported functions to cast text into a specific data type. The list below is intended as guideline and may differ from your database documentation.

If the problem persists, log a support ticket regarding this logical defect to Strategy Support team and attach the DSSErrors.log file.
OperationNew Instance CreatedExecute dossierYesSwitch bookmarkYesReset dossierYesRepromptingYesRefresh (with Web Preference > Project Defaults > History List > Duplicate Message on Repromt or Refresh setting on)YesRefresh (with Web Preference > Project Defaults > History List > Duplicate Message on Repromt or Refresh setting off)NoExporting a dossierNoUndo or redoNoSortingNoDrillingNoSwitching pagesNoSwitching chaptersNoChanging FiltersNoAdding a datasetNoEditing a datasetNoDeleting a datasetNoDatabaseQuoting Character for Namespaces, Tables, Column IdentifiersRelevant Formats if Using Default Mapping Type as VarCharTeradata" (quote)Date

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

"yyyy-mm-dd"

Oracle Database" (quote)Date

"dd-mm-yyyy"

Time

"yyyy-mm-dd hh:nn:ss"

Microsoft SQL Server[ ] (square bracket)
Note: if the identifier uses character

, you can escape by using

]]

in your SQL statement.Date

"yyyy-mm-dd"

Time

"hh:nn:ss.0000000"

TimeStamp

"yyyy-mm-dd hh:nn:ss.0000000"

Amazon Redshift" (quote)Date

“yyyy-mm-dd"

IBM DB2" (quote) PosgreSQL" (quote)Date

"yyyy-mm-dd"

Azure Synapse Analytics[ ] (square bracket)Date

"yyyy-mm-dd"

Exasol" (quote)Date

"yyyy-mm-dd"

Oracle MySQL` (backtick)Date

"yyyy-mm-dd"

SAP HANA" (quote)Date

"yyyy-mm-dd"

Time

"yyyy-mm-dd hh:nn:ss"

Google BigQuery` (backtick)Date

"yyyy-mm-dd"

Snowflake" (quote)

Secure Transactions

For certified gateways, the submitted SQL statement through transactions is secured with parameterized queries. See KB485174: Secure text inputs for transaction services reports and transaction enabled documents for more information.

References

KB483540: Unified Quoting Behavior for Warehouse Identifiers
KB485174: Secure text inputs for transaction services reports and transaction enabled documents

Comment

0 comments

Details

Knowledge Article

Published:

October 13, 2022

Last Updated:

March 21, 2024