Strategy ONE
Customizing SQL Queries: Freeform SQL
The table below summarizes the Freeform SQL VLDB properties. Additional details about each property, including examples where necessary, are provided in the sections following the table.
| Property | Description | Possible Values | Default Value |
|
Provides the flexibility to display or hide warnings when a Freeform SQL statement returns an empty result. |
Do not turn off warnings for Freeform SQL statements with empty results, such as updates. Turn off warnings for Freeform SQL statements with empty results, such as updates. Turn off warnings for Freeform SQL statements that return multiple result sets with an empty first result set and return second result set, such as stored procedures. |
Do not turn off warnings for Freeform SQL statements with empty results, such as updates. |
|
|
Lets you validate Transaction Services reports that use XQuery. |
User-defined. |
false |
Ignore Empty Result for Freeform SQL
Ignore Empty Result for Freeform SQL is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.
The Ignore Empty Result for Freeform SQL VLDB property provides the flexibility to display or hide warnings when a Freeform SQL statement returns an empty result.
Freeform SQL is intended to be used to return results that can be displayed on a Freeform SQL report. However, Freeform SQL can also be used to execute SQL statements that create tables, update tables, or perform other database maintenance tasks. These types of actions do not return any results and therefore would return a warning when executing a Freeform SQL report. If you routinely use Freeform SQL for these purposes, you can hide these warnings since an empty result set is expected.
This VLDB property has the following options:
- Do not turn off warnings for Freeform SQL statements with empty results, such as updates (default): This option allows warnings to be displayed when a Freeform SQL statement causes a Freeform SQL report to return an empty result. This is a good option if you use Freeform SQL to return and display data with Freeform SQL reports.
- Turn off warnings for Freeform SQL statements with empty results, such as updates: Select this option to hide all warnings when a Freeform SQL statement causes a Freeform SQL report to return an empty result. This is a good option if you commonly use Freeform SQL to execute various SQL statements that are not expected to return any report results. This prevents users from seeing a warning every time a SQL statement is executed using Freeform SQL.
However, be aware that if you also use Freeform SQL to return and display data with Freeform SQL reports, no warnings are displayed if the report returns a single empty result.
- Turn off warnings for Freeform SQL statements that return multiple result sets with an empty first result set and return second result set, such as stored procedures: Select this option to hide all warnings when a Freeform SQL report returns an initial empty result, followed by additional results that include information. Stored procedures can sometimes have this type of behavior as they can include statements that do not return any results (such as update statements or create table statements), followed by statements to return information from the updated tables. This prevents users from seeing a warning when these types of stored procedures are executed using Freeform SQL.
If you select this option and a Freeform SQL report returns only a single empty result, then a warning is still displayed.
Levels at Which You Can Set This
Database instance and report
XQuery Success Code
XQuery Success Code is an advanced property that is hidden by default. For instructions on how to display this property, see Viewing and Changing Advanced VLDB Properties.
The XQuery Success Code VLDB property lets you validate Transaction Services reports that use XQuery. MicroStrategy Transaction Services and XQuery allow you to access and update information available in third-party web services data sources. The steps to create a Transaction Services report using XQuery are provided in the Advanced Reporting Help.
When Transaction Services and XQuery are used to update data for third-party web services, sending the data to be updated is considered as a successful transaction. By default, any errors that occur for the third-party web service during a transaction are not returned to MicroStrategy.
To check for errors, you can include logic in your XQuery syntax to determine if the transaction successfully updated the data within the third-party web service. Just after the XQuery table declaration, you can include the following syntax:
<ErrorCode>{Error_Code}</ErrorCode>
<ErrorMessage>{Error_Message}</ErrorMessage>
In the syntax above:
Error_Codeis a variable that you must define in your XQuery statement to retrieve the success or error code from the third-party web service, for the action that attempts the transaction. The logic to return an error code depends on the third-party web service that you are attempting to perform the transaction on.Error_Messageis either a static error message that you supply, or a variable that you must define in your XQuery statement to retrieve any resulting error message from the third-party web service.
By including this syntax in your XQuery statement, the XQuery Success Code VLDB property is used to validate the transaction. The information returned by the Error_Code variable is compared to the value supplied for the XQuery Success Code. By default, the XQuery Success Code is defined as "false", but you can type any valid string. If the Error_Code and XQuery Success Code are identical, then the content in the Error_Message is not returned and the transaction is returned as a success. However, if the Error_Code returns any value other than the XQuery Success Code, the content for the Error_Message is returned. This lets you validate each transaction that is sent to the third-party web service.
Levels at Which You Can Set This
Database instance and report