8.1.1.5 Other Queries

The Data Analysis Tool also supports providing SQL queries directly in the analysis XML file.

A query has two components: the query title and the query itself. As queries often contain characters that are “reserved” in XML, you should follow the example below for “escaping” the SQL to ensure that it does not become corrupted.

<QUERIES>
<SQLQUERY title="title">
select col1, col2 from some_table
where some_condition
</SQLQUERY>
</QUERIES>

The following XML sample code illustrates use of the <QUERIES> element:

<ANALYSIS>
<QUERIES>
<SQLQUERY title="FO Transaction Roles"><![CDATA[ select
FOT.mantas_PRODUCT_TYPE_CD,
FOTPS.PARTY_ROLE_CD, count(1) as RoleCt
from FO_TRXN_STAGE FOT, FO_TRXN_PARTY_STAGE FOTPS
where FOT.TRXN_INTRL_ID = FOTPS.TRXN_INTRL_ID

This code runs the query in the <SQLQUERY> element and writes the results to the output file. For SQL queries, the results are always in HTML. Your code can contain any number of <SQLQUERY> elements. The system runs each query in sequence after the other components of analysis are complete.

SQLQUERY Element Rules

Several cautions and notes are specific to the <SQLQUERY> element:

• If your query contains characters that XML standards reserve, such as > or <, you must place your query within a CDATA block.
• Verify that no white space exists between the SQL query opening tag and the CDATA tags , such as <![CDATA[ ...) and the closing tag , such as ...]]>.
• Processing extracts column headers in the output from the SQL query itself. When performing calculations in return columns, it is best to alias the return columns for output
• Line breaks and comments in the SQL are acceptable, but you should use /* */ style comments in lieu of single-line comments for safety.
• The tool does not perform any schema-name substitution. Therefore, verify that any schema names match the database contents. The database user , such as ATOMIC, has aliases for most tables you may must analyze. Thus, running the tool as ATOMIC should prevent you from needing schema names in queries.