Are there any rules for Excel Reports SQL in QC/ALM?

The following describes Excel Reports SQL Writing Rules in QC/ALM.

These rules derive from QC 10.00 changes, where the former literal SQL is replaced by prepared statements.

These changes apply to QC 10.00 after Patch 15 and all future HP QC/ALM future versions 

1. Parameters can be used as possible column values only (WHERE part).

2. Parameter can be used for a single value only (and not as a list of possible values).

3. Parameter cannot be used for multiple values substitution.

For example:
This following SQL statement is incorrect:
SELECT * FROM BUG WHERE BG_BUG_ID IN (@p@), p=1,2,3
The correct form is:
SELECT * FROM BUG WHERE BG_BUG_ID IN (@p1@, @p2@, @p3@,), p1=1, p2=2, p3=3

4. Parameters cannot be used as table aliases (AS usage).

5. Neither parameters nor parameter values can be enclosed by quotes: parameter type resolving occurs on prepared statement compilation.

6. Parameters cannot be part of another string.

For example, instead of:
SELECT * FROM BUG WHERE BG_PRIORITY LIKE '%@p@%', p=Low

write:
SELECT * FROM BUG WHERE BG_PRIORITY LIKE @p@, p=%Low%

Alternatively use db type dependent concatenation ('%' + @p@ + '%' (MSSQL) or '%' || @p@ || '%' (Oracle) WHERE p=Low).

7. Do not use special characters (non-word characters) in parameter names.

8. The ‘?’ character in SQL text should not be used.

    Instead, concatenate chr(63) in Oracle and char(63) in SQL to the rest of the SQL statement text.

9. Do not use the ‘//’ combination in SQL. This combination is used for comments mark.

For example:
SELECT 'td://actions_upgrade.alexk.localhost:8080/qcbin/AnalysisModule-00000002220154988?EntityType=IAnalysisItem&EntityID=' + AI_ID FROM ANALYSIS_ITEMS

To resolve the problem, use string concatenation:
MSSQL:
SELECT 'td:/' + '/actions_upgrade.alexk.localhost:8080/qcbin/AnalysisModule-00000002220154988?EntityType=IAnalysisItem&EntityID=' + AI_ID FROM ANALYSIS_ITEMS
ORACLE:
SELECT 'td:/' || '/actions_upgrade.alexk.localhost:8080/qcbin/AnalysisModule-00000002220154988?EntityType=IAnalysisItem&EntityID=' + AI_ID FROM ANALYSIS_ITEMS

Double quotes discovering SQL statements.

Use the following SQL statements from SQL tool or in Site Admin in order find all Excel reports with ‘double quotes’ problem.

MSSQL:
SELECT

      AI_ID,

      AI_NAME,

      AI_FILTER_DATA,

'td://[project_name].[domain_name].[server_name]:[port]/qcbin/[AnyModule]?EntityType=IAnalysisItem&EntityID='

      + CAST(AI_ID AS varchar) AS Excel_Report_URL

FROM ANALYSIS_ITEMS

WHERE (

      (AI_FILTER_DATA LIKE '%''@%'

                  AND AI_FILTER_DATA LIKE '%DefaultValue="''%')

                OR AI_FILTER_DATA LIKE '%''''@%'

                OR AI_FILTER_DATA LIKE '%DefaultValue="''''%'

)

AND   AI_TYPE = 'ExcelReport'

ORACLE:
SELECT

      AI_ID,

      AI_NAME,

      AI_FILTER_DATA,

      'td://[project_name].[domain_name].[server_name]:[port]/qcbin/[AnyModule]?EntityType=IAnalysisItem&EntityID=' || AI_ID AS Excel_Report_URL

FROM ANALYSIS_ITEMS

WHERE(

      (AI_FILTER_DATA LIKE '%''@%'

            AND AI_FILTER_DATA LIKE '%DefaultValue="''%')

                OR AI_FILTER_DATA LIKE '%''''@%'

                OR AI_FILTER_DATA LIKE '%DefaultValue="''''%'

)

Where:
[project_name] – project name the sql executed on
[domain_name] – domain name the mentioned project belongs to
[server_name] – qc server name
[port] – server port

Limitation: parameters cannot be located inside a comment block or line.