Scripts in SQL

Intellicus supports writing an executable script block in queries. You can insert the script code anywhere in the SQL. It should be within start and end tags (<@% and %@>). This executable block returns a string, which replaces the script block in the SQL query.

The block is enclosed in tags:

Script block start tag: <@%
Script block end tag: %@>

A script block may have a valid java script code, including all java script programming constructs E.g. logical conditions (if…else clause) on which the block can take decisions.

You can access various objects from within the script block based on run time context.

Objects accessible from SQL script

Parameters

To refer any parameter within this executable block user has to use the object model i.e. he/she has to write

params(“parameterName”).getXXX()

Where, parameterName is the name of the parameter and getXXX are various getter methods for different attributes of the input parameter:

getValue(): Returns value of the parameter. For example, params(“prmCity”).getValue(). Return type is string.
getDataType(): Returns data type of the parameter. It can be: NUMBER (for numeric parameters), CHAR (for character parameters), DATE (for date type parameters) and BOOL (for Boolean parameters). It will return NULL if parameter datatype is unknown.

If you do not append any attribute (for example, params(“prmEmpl”), it is assumed as parameter itself. You can compare this to null to check existence of the parameter.

Note: Only getValue() is supported for SYSTEM type parameters or the parameters whose definition is not available.

Examples

Example 1. Check if the parameter prmEmpNo exists and its value is blank. If so, append a condition to where clause in the SQL.

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

Example 2. Check if prmSelectTable exists. If it does, return value of the parameter as table name. If it does not, return table name as emp.

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

Example 3. Check if parameter p_EmpNo exists. If it does not exist, call procedure with any arguments. If it exists, then it call the procedure that takes one argument and it passes this parameter as the argument of procedure.

<@%
if(params(“p_EmpNo”) != null)
{
return “EMP_DETAILSWITHARG <%p_EmpNo%>”
}
return “EMP_DETAILS”;
%@>

Report Layout

To refer report layout from SQL script block, you have to use the object model as:

rpt.layout.getXXX()

You can add script block to an SQL inside a standard report or to an SQL inside a Query Object. In both cases, when SQL is verified in SQL Editor for compile errors and result set, the report layout object may not be available to the script.

When the report is executed – Preview, Run, or Run Ad hoc report using Query Object, then report layout object is available to the script.

So, you essentially check whether your SQL is running for result set or for report run, before accessing report layout object.

var arl = rpt.layout.getArl();
if (arl != null)
{ var fields = arl.getFields();
}
OR
var irl = rpt.layout.getirl();
if (irl != null)
{ var fields = irl.getFields();
}

Below is a long example of accessing various parts of report layout to dynamically construct an optimized SQL for an Ad hoc report.

Example 4. Check if SQL is running for an Ad hoc report. If yes check the fields used in various constructs of Ad hoc report layout – Select, Filters, Sort, Group, Chart, and cross tab. Create select clause of SQL with only the fields used in any of the ad hoc report construct.

SELECT
<@%
// check if report run
var arl = rpt.layout.getArl();
if (arl != null)
{
var fields = arl.getFields();
var myArray = new Array();
var str = "";
var i = 0;

for (i = 0; i < fields.getCount(); i++)
{
var field = fields.get(i);

if ("TRUE" == field.getDisplayEnabled())
{
myArray[field.getName()] = field.getName();

}

}
// check Group section
var grpCount = rpt.layout.getGroupsCount();
for (var grpIndex = 0; grpIndex < grpCount; grpIndex++)
{
var grp = rpt.layout.getGroup(grpIndex);
myArray[grp.getFieldName()] = grp.getFieldName();

}
// check Filter section
var fltrCount = rpt.layout.getFiltersCount();

for (var fltrIndex = 0; fltrIndex < fltrCount ; fltrIndex++)
{
var fltr= rpt.layout.getFilter(fltrIndex);
myArray[fltr.getFieldName()] = fltr.getFieldName();

}
// check sort section
var sortCount = rpt.layout.getSortParamsCount();

for (var sortIndex = 0; sortIndex < sortCount ; sortIndex++)
{
var sortParam = rpt.layout.getSortParam(sortIndex);
myArray[sortParam.getFieldName()] = sortParam.getFieldName();

}
// check Highlighting section

var adhcConditions = arl.getAConditions();

if(adhcConditions != null)
{

var adhcConCount= adhcConditions.getCount();
for (var adhcConIndex= 0; adhcConIndex< adhcConCount; adhcConIndex++)
{
var adhcCondition = adhcConditions.get(adhcConIndex);
var clause = adhcCondition.getAClause(0);
myArray[clause.getFieldName() ] = clause.getFieldName() ;
}
}
// check chart section

var chart = arl.getChart();

if(chart != null)

{

var XCount = chart.getChartXAxisCount();
for (var index = 0; index < XCount; index++)
{
var XAxis = chart.getChartXAxis(index);
myArray[XAxis.getFieldName()] = XAxis.getFieldName();
}

var seriesEnum = chart.getSeriesEnum()

var seriesCount = seriesEnum.size();


for (var index = 0; index < seriesCount; index++)
{
var series = seriesEnum.get(index);
myArray[series.getFieldName()] = series.getFieldName();

}
}

// check Matrix section
var matrix = arl.getMatrix();

if(matrix != null)
{

var summaries = matrix.getMatrixSummaries();

var summarySize = summaries.size();



for (var index = 0; index < summarySize; index ++)
{
var summary = summaries.get(index);
myArray[summary.getFieldName()] = summary.getFieldName();

}

var xAxes = matrix.getMatrixXAxes();

var xAxesSize = xAxes.size();



for (var index = 0; index < xAxesSize; index ++)
{
var xAxis = xAxes.get(index);
myArray[xAxis.getFieldName()] = xAxis.getFieldName();

}

var yAxes = matrix.getMatrixYAxes();

var yAxesSize = yAxes.size();



for (var index = 0; index < yAxesSize; index ++)
{
var yAxis = yAxes.get(index);
myArray[yAxis.getFieldName()] = yAxis.getFieldName();

}
}


var FormulaField= new Array("Ccy1amount1","Ccy1amount2");
for (var cnt= 0; cnt< FormulaField.length; cnt++)
{
myArray[FormulaField[cnt]] = FormulaField[cnt];
}



for(var key in myArray)
{
str += myArray[key]+",";
}
return str.substring(0, str.length - 1);
}
else
{
return " * ";
}
%@>
FROM Call_Log

Insert script block in SQL

Figure 2: Script Editor with the query

Kyvos 2024.12

Scripts in SQL

Objects accessible from SQL script

Parameters

Examples

Report Layout

Related content