SUMMARY:
The impact is limited to Strategy SQL Engine product only with reports attempting to use Insert Mid Statements, Insert Post Statement, Insert Pre Statement, Report Post Statement, Report Pre Statement, Table Post Statement and Table Pre Statement. This document explains how Statement 5 is applied in the current sample document.
SYMPTOM:
When changing the Pre/Post SQL statement 5 in the VLDB properties of a Strategy Report the semicolon is removed when generating SQL as shown below.
The image below shows the setup for Report Pre SQL Statement 5.
The image below shows the SQL view of the Report Pre Statement 5s output.
CAUSE:
Strategy can support multiple (more than 5) pre-statements to put in a report. The first four statement VLDB properties, each can contain single SQL statement. The last statement can contain multiple SQL statements concatenated by “;”.
In our current design, report pre/post statement 5 is different from 1-4. In statement 5:
* Single semi-colon is used as a delimiter when multiple statements are being passed to warehouse;
* Double semi-colon is used to skip the first rule and be replaced by single semi-colon.
VIDEO:
A video with an example of this issue is shown below
ACTION:
To achieve using the semicolon in the environment, add another semicolon in Pre SQL Statement as shown below.
The image below shows the changes in VLDB properties.
The image below is the SQL view with two semicolons:
TO LEARN MORE:
Supplemental Reference for System Administration Section
Chapter 1: Section: SQL Generation and Data Processing: VLDB Properties.
