Editing SQL Statement

Applies to: Kyvos Reporting

The Edit tab in SQL Designer allows you to write and modify complex SQL queries that may not be feasible through the Design tab.

When you switch from the Design tab to the Edit tab, the query created in Design is automatically constructed and displayed as a full SQL statement. You can then edit or extend this SQL as needed.

Context Help

As you type your query in the Edit tab, Context Help provides real-time syntax assistance. Suggestions appear in a pop-up list as you type and disappear once a selection is made, helping ensure valid SQL syntax.

Preventing SQL Overwrite

If you manually modify the SQL in the Edit tab and later return to the Design tab to make changes, a prompt appears when you come back to Edit:

Yes: Overwrites the Edit tab content with the updated SQL from the Design tab.
No: Retains the manually written SQL in the Edit tab.

Note

The SQL defined in the Edit tab is used as the final SQL during report generation.

Important

If SQL is manually specified in the Edit tab, the fields used in the query will not appear in the Filter tab.

Using Parameters in SQL (Dynamic SQL)

You can include parameters in SQL using the syntax:

<%ParameterName%>

Example:

SELECT * FROM EmpTbl WHERE <%PrmWhrCls%>

This allows dynamic construction of SQL statements at runtime.

JavaScript Code Blocks in SQL

To construct fully dynamic SQL, embed JavaScript code using:

<@% 
  // Your JavaScript logic here
  return "some SQL fragment";
%@>

You can access parameter values using:

params("parameterName").getAttribute("Value")
params("parameterName").getAttribute("DataType")

Examples of Dynamic SQL

Example 1: Conditional WHERE clause using a parameter

SELECT * FROM emp WHERE 1=1
<@%
if (params("prmEmpNo") != null && params("prmEmpNo").getValue() != "") {
  return " AND empno IN (<%prmEmpNo%>)";
}
return "";
%@>

Example 2: Dynamic table selection

SELECT * FROM 
<@%
if (params("prmSelectTable") == null) {
  return "emp";
}
return params("prmSelectTable").getValue();
%@>

Example 3: Multi-parameter dynamic WHERE clause

SELECT LOCATIONID, LOCATIONNAME, LOCTYPEID, REGNID 
FROM LOCATIONMASTER 
WHERE 1=1
<@%
var filter = "";
if (params("prmloctype") != null && params("prmloctype").getValue() != "") {
  filter += " AND LOCATIONTYPEID IN (<%prmloctype%>)";
}
if (params("prmRegion") != null && params("prmRegion").getValue() != "") {
  filter += " AND REGNID = <%prmRegion%>";
}
return filter;
%@>

Find Function in Edit Tab

The Edit tab includes a Find button that helps locate specific text within long SQL queries. This improves navigation and editing efficiency in large statements.