Editing SQL Statement

Writing Power SQL

When you switch from the Design tab to the Edit tab, the SQL in the Design tab is constructed and displayed as a complete SQL statement in the later. Using the Edit tab, you can view and write complex SQL statements that cannot be defined using the Design tab.

During Query writing, you can make use of Context Help. It assists you in correct code (syntax) formation. As you type the code, you can view the context help to select the options. A window pops up for you to choose the right option and disappears upon choosing the option.

Figure 8: Editing SQL Statement

To prevent a complex query (defined in the Edit tab) from being overwritten, when you switch over to the Design tab, make some changes, and get back to Edit tab; you will be prompted with a dialog.

Figure 9: SQL Overwrite Prompt

If you select Yes your changes in the Edit tab will be overwritten, because the SQL in the Design tab will be reconstructed. If you select ‘No’ then the SQL in the Edit tab will remain intact and shall be used as the final SQL.

Note: The SQL statement in the Edit tab will be used as the final SQL for compilation.

Important: If SQL is manually specified in Edit tab (not constructed using design tab), its field details will not be available in Filter tab.

Dynamic SQL Queries using parameters

To include a parameter in the SQL Query, specify a parameter enclosed within <% and %>.

Example: To dynamically decide the where clause:

SELECT * FROM EmpTbl WHERE <%PrmWhrCls%>

Dynamic SQL javascript code block

Use javascript code block enclosed by <@% and %@> to create a dynamic SQL Query. After execution, the code block should return a string that will be replaced in the SQL query.

You can use following in the code block:

• if else

• Parameter value and parameter data type

Syntax of Code block

<@% >
[Executable block, which returns a string]
<%@>

Syntax to use parameter in code block

params(“parameterName”).getAttribute()

Attributes: “Value” to get value of the parameter and “DataType” to get data type of the parameter.

Examples

Example 1. Check if parameter “prmEmpNo” exists. If it does and its value is not blank, return the value, else return nothing.

Select * from emp where 1=1
<@%
if(params(“prmEmpNo”) != null &&
params(“prmEmpNo”).getValue() != “”)
{
return ” AND empno in <%prmEmpNo%>”;
}
else
{
return “”;
}
%@>

Example 2. Check if parameter “prmSelectTable” exists. If it does not, return table name as “emp” else (if it exists) value of the parameter must be used as table name.

Select * from
<@% if(params(“prmSelectTable”) == null) { return “emp”; else return params(“prmSelectTable”).getValue(); } %@>

Example 3. Construct WHERE clause dynamically (for LOCATIONTYPEID and REGNID fields).

select LOCTIONID, LOCTIONNAME,LOCTYPEID,REGNID from LOCATIONMASTER where 1=1
<@%
var v_Str;
v_Str = ”;
var v_Str1;
var v_Str2;
var v_Str1=”;
var v_Str2=”;
if(params(“prmloctype”) != null &&
params(“prmloctype”).getValue() != “”)
{
v_Str1 = ” AND LOCATIONTYPEID in (<%prmloctype%>)”;
}
else
{
v_Str1 = “”;
}

if(params(“prmRegion”) != null &&
params(“prmRegion”).getValue() != “”)
{
v_Str2 = ” AND REGNID = <%prmRegion%> “;
}
else
{
v_Str2 = “”;
}

v_Str = v_Str1 +v_Str2;
return v_Str;
%@>

Find Button

The Edit tab has a find button. For long queries (refer to the figure on previous page), you can search the query for a search string to reach the location quickly.