New SPL

Read(23) Label: spl editor, compositetable,

esProc SPL Editor lets you read data from a database table or a file (Extract), convert the extracted data via a certain operation, such as grouping, sorting or join (Transform), and write the data into a target composite table file (Load).

Click File -> New SPL and select a cell. Right-click the cell and select Function assist editor to get the following pop-up, on which you select a function and edit the parameters to generate a SPL statement.

 

Save the newly-created SPL process as a file with spl extension. Same as the dfx file, the spl file can be executed on the esProc main screen or from the command line. About the command line execution, you can refer to Command Line in esProc Tutorial.

 

Note:

1. You can’t change the function over an edited cell (except that you clear the cell); but you can edit the function’s parameter values.

2. Manual editing can’t be synchronized to the Function assist editor.

3. Only composite-table-related functions are listed in Function assist editor.

 

Here we use an example to illustrate how to edit function through Function assist editor.

Step 1: Create a new spl file.

Click New SPL under File to generate a cellcest:

 

Step 2: Edit cell A1 to connect to demo data source.

Select A1 and right-click it to choose Function assist editor. On the pop-up window, you select connect(db) function and select “demo” as the Data source name value.

 

Click OK to get the following SPL statement for A1:

Step 3: Edit cell A2 to read EMPLOYEE table from demo.

Select A1 and right-click it to choose Function assist editor. Select cell A1(DB), select cursor(sql) function, enter SQL statement “select * from EMPLOYEE” as the SQL value, and select function options @dx.

Click OK to get the following SPL statement for A2:

 

Step 4: Edit cell A3 to group EMPLOYEE table by DEPT and calculate average of SALARY.

Select A3 and right-click it to choose Function assist editor. Select cell A2(Cursor), select groupx() function, enter grouping expression DEPT and rename it Dept, and enter aggregate expression avg(SALARY) and rename it AvgSalalry.

Click OK to get the following SPL statement for A3:

Now the extracted data has been converted to what you need and is ready to be exported to a target file.

 

Step 5: Create composite table file Employee.ctx.

Select A3 and right-click it to choose Function assist editor. Select file() function and enter D:\Employee.ctx as the file name value.

Click OK to get the following SPL statement for A4:

 

Step 6: Create the composite table’s base table according to a file.

Select A3 and right-click it to choose Function assist editor. Select cell A4(File), select create() function, set two field names – Dept (dimension field) and AvgSalary.

Click OK to get the following SPL statement for A5:

 

Step 7: Write cursor records to A5’s entity table.

Select A3 and right-click it to choose Function assist editor. Select cell A5(CTX), select append() function, enter A3 as the Cursor name value, and use the default setting (Close file after append).

Click OK to get the following SPL statement for A6:

 

Step 8: Save and run the spl file.

The execution generates a composite table file Employee.ctx on D disk.

You can view the file through Data File Tool.

You can refer to Composite Tables in esProc Tutorial to learn more about composite tables.