<< Click to Display Table of Contents >> Current:  Script  Script Data Set

Script Data Set implements the functions provided by SQL Data Set over script language and data interface. Such a method avoids the design limitations on SQL Data Set and makes the operations on data sets more flexible. Script Data Set provides some common Javascript function interfaces in the tool library to allow for querying data by programming. Although these interfaces are limited to the set functions, they can meet the existing daily work.

Besides, Script Data Set provides the cross-database data set union function that is not supported by SQL Data Set. Similar to union query in SQL, this function shows the data from different tables on one table through the script join data set method as required. Script Data Set provides more powerful functions than the union query function of SQL. SQL union data set requires that all data lists must come from the same database, whereas Script Data Set can connect the data lists from different databases. Assume that the operating data of a company is saved in DB2 database and the management data of the company is saved in Oracle database. The operating data and management data can be unioned to a new data set list for analysis through Script Data Set.

The user can create a script data set to mix data from different data sources onto a lookup table. For example, one is the Sql table of the access database, and the other is an embed-type query table. The data is first executed and then joined together to form a new table.

 

❖Editing interface

First start the interface for editing the script data set. (How to start, please refer to the Create Data Set->Script Data Set->Create Script Data Set). In the open interface, users can write script statements to achieve multi-table connections.

There are three areas in the interface. The top is a tree structure providing tool functions, the middle is an editing area for inputting scripts, and the bottom is an information check feedback field. When the tree node is clicked, the text content of the node in the point is automatically inserted into the script editing area, and the cursor is at the position. The content of the current editing area is automatically checked at regular intervals. The result of the check is displayed in the feedback field.

The purpose of this edit interval is to return a query result. There may be other results in the middle, but in the end it must be returned as a result of the query. The main purpose of a Script Data Set is to mix data from different data sources onto a lookup table. Then you need to perform other queries first, and then join the tables through some Join function.

Script Data Set can implement links of different types of data sets. The types and corresponding identifiers of each data set are shown in the following figure:

 

❖Executing the function of query

A defined data set can be returned by calling the execute function.

execute(Scriptable scope, int type, String path, Object allCols)

• Parameters:

scope: it is the action scope of running this script. Generally this is used, which refers to the current action scope.

type: it refers to the type of data set, which supports 10 types: SQL, EXCEL, SCRIPT, CUSTOM, CLOUD, EMBED,COMPOSITE， MONGO， DATA_FLOW, MDX. 

path: it is the path and name of data set. Use '/' to connect if there is a directory. 

allCols: it refers to the column of data set expression is visible when it is true; and the column of data set expression is invisible when it is false.

• Return:

One query result 

• For example:

•execute(this, EMBED, "folder1/query1", true);

 

❖Join Function

Link the query results of any two executed data sets, and return the new query results. It requires matching the data type of key words to be mapped. 

join(Scriptable scope, int jhint, int jop, Object jleft, Object jright, Object jlkeys, Object jrkeys, Object jlcols, Object jrcols)

• Parameters:

scope: it refers to the action scope of running this script. Generally this is used, which refers to the current action scope.

jhint: it  is the hint of join. LEFT_MAIN takes the left table as the main table, RIGHT_MAIN takes the right table as the main table, and FINAL_JOIN as the final table.

TEMP_JOIN: it is a temporary table, which is the table to be recycled. 

jop: it is concatenation. JOIN is the in-connection; LEFT_JOIN left join; RIGHT_JOIN right join; FULL_JOIN full join.

jleft: left table 

jright: right table

jlkeys: connective field of left table 

jrkeys: connective field of right table 

jlcols: which fields left by left table 

jrcols: which fields left by right table

• Return:

One query result

• Example:

join(this, FINAL_JOIN | LEFT_MAIN, LEFT_JOIN, query1, query2, [1], [3], [1,2], [3,1]);// The first column of the left table, query1, joins the third column of the right table, query2, leaving columns 1 and 2 of query1, and columns 3 and 1 of query2.

 

❖Union Function

Link the query results of any two executed queries, and return the new query results. The number of field of two queries is matching, and the data type is matching. 

union(Scriptable scope, Object ugrids)

• Parameters：

scope: it is the action scope of running this script. Generally this is used, which refers to the current action scope.

ugrids: which tables should request the intersection set. If the column is not matching, mistakes will occur. This parameter is an array.

• Return:

One query result 

• Example:

union(this, [a, b]);// Combine a and b.

 

❖Taking Column Function

Extract the data of certain column from a query result, and return a new query result.

columns(Scriptable scope, Object cgrid, Object ccols)

• Parameters:

scope: it is the action scope of running this script. Generally this is used, which refers to the current action scope.

cgrid: it refers to which query results are extracted.

ccols: it refers to which column is extracted.

• Return:

One query result

• Example:

columns(this, a, [3,1]);// Extract the third and first columns of a.

 

❖Sorting Function

Sorting certain columns in the query result, and return a new query result. 

sort(Scriptable scope, Object sgrid, Object scols, Object sascs)

• Parameters:

scope: it refers to the action scope of running this script. Generally this is used, which refers to the current action scope.

sgrid: it refers to which query result is sorted.

scols: it refers to which field is sorted.

sascs: it what sorting types are used for each field: true (ascending), false (descending).

• Return:

One query result 

• Example:

sort(this, a, [3,1], [false, true]);// First descend the third column of a, and then ascend the first column of a.

➢For example, it wishes to join the query result of sybase database and the query result of one excel file as the data source. Suppose that the data type of key words of these two queries are matching. 

var lt = execute(this, SQL, "sybase/customer"); // left table

var rt = execute(this, SQL, "Excel_Coffee_chain"); //  right table

var lkeys = [0];// join the 0 column of left table

var rkeys = [1];// join the 1st column of right table 

var lcols = [0, 1];// save the 0 and 1st of columns of left table 

var rcols = [0, 2];// save the 0 and 2nd columns of right table

join(this, FINAL_JOIN, LEFT_JOIN, lt, rt, lkeys, rkeys, lcols, rcols); // undertake left join, and this result is final, then return the new query result.