SQL template writing rules

SQL statements

An SQL statement can be any valid SQL statement that the related JDBC is able to execute. The SQL template code is a group of SQL statements. The basic rules to write an SQL statement in the SQL template editor are:

• An SQL statement must end with ;.

• An SQL statement can span lines. In this case, no line should be ended with ; except the last one.

Comment lines

A comment line starts with # or --. Any line that starts with # or -- will be ignored in code generating.

The <%...%> syntax

This syntax can span lines. The following list points out what you can do with this syntax and what you should pay attention to.

• You can define new variables, use Java logical code like if, for and while, and also get parameter values.

  For example, if you want to get the FILE_Name parameter, use the code as follows:

<%
         String filename = __FILE_NAME__;
         %>

• This syntax cannot be used within an SQL statement. In other words, it should be used between two separated SQL statements.

  For example, the syntax in the following code is valid.

#sql sentence
         DROP TABLE temp_0;
         <%
         #loop
         for(int i=1; i<10; i++){
         %>
         #sql sentence
         DROP TABLE temp_<%=i %>;
         <%
         }
         %>
         #sql sentence
         DROP TABLE temp_10;

In this example, the syntax is used between two separated SQL templates: DROP TABLE temp_0; and DROP TABLE temp_<%=i%>;.

The SQL statements are intended to remove several tables beginning from temp_0. The code between <% and %> generate a sequence of number in loop to identify tables to be removed and close the loop after the number generation.

• Within this syntax, the <%=...%> or </.../> syntax should not be used.

<%=...%> and </.../> are also syntax intended for the SQL templates. The below sections describe related information.

The <%=...%> syntax

This syntax cannot span lines and is used for SQL statement. The following list points out what you can do with this syntax and what you should pay attention to.

• This syntax can be used to generate any variable value, and also the value of any existing parameter.

• No space char is allowed after <%=.

• Inside this syntax, the <%...%> or </.../> syntax should not be used.

The statement written in the below example is a valid one.

#sql sentence
         DROP TABLE temp_<%=__TABLE_NAME__ %>;

The code is used to remove the table defined through an associated component.

For more information about what components are associated with the SQL templates, see What is a Job design?.

For more information on the <%...%> syntax, see The <%...%> syntax.

For more information on the </.../> syntax, see the following section.

The "</.../>" syntax

This syntax cannot span lines. The following list points out what you can do with this syntax and what you should pay attention to.

• It can be used to generate the value of any existing parameter. The generated value should not be enclosed by quotation marks.

• No space char is allowed after </ or before />.

• Inside this syntax, the <%...%> or <%=...%> syntax should not be used.

The statement written in the below example is a valid one.

#sql sentence
         DROP TABLE temp_</TABLE_NAME/>;

The statement identifies the TABLE_NAME parameter and then removes the corresponding table.

For more information on the <%...%> syntax, see The <%...%> syntax.

For more information on the <%=...%> syntax, see The <%=...%> syntax.

The following sections present more specific code used to access more complicated parameters.

Code to access the component schema elements

Component schema elements are presented on a schema column name list (delimited by a dot "."). These elements are created and defined in components by users.

The below code composes an example to access some elements included in a component schema. In the following example, the ELT_METADATA_SHEMA variable name is used to get the component schema.

<%
         String query = "select ";
         SCHEMA(__ELT_METADATA_SHEMA__);
         for (int i=0; i < __ELT_METADATA_SHEMA__.length ; i++) {
         query += (__ELT_METADATA_SHEMA__[i].name + ",");
         }
         query += " from " + __TABLE_NAME__;
         %>
         <%=query %>;

In this example, and according to what you want to do, the __ELT_METADATA_SHEMA__[i].name code can be replaced by __ELT_METADATA_SHEMA__[i].dbType, __ELT_METADATA_SHEMA__ [i].isKey, __ELT_METADATA_SHEMA__[i].length or __ELT_METADATA_SHEMA__[i].nullable to access the other fields of the schema column.

The extract statement is SCHEMA(__ELT_METADATA_SHEMA__);. In this statement, ELT_METADATA_SHEMA is the variable name representing the schema parameter to be extracted. The variable name used in the code is just an example. You can change it to another variable name to represent the schema parameter you already defined.

For more information on component schema, see Basic settings tab.

Code to access the component matrix properties

The component matrix properties are created and changed by users according to various data transformation purposes. These properties are defined by tabular parameters, for example, the operation parameters or groupby parameters that users can define through the tSQLTemplateAggregate component.

To access these tabular parameters that are naturally more flexible and complicated, two approaches are available:

• The </.../> approach:

</.../> is one of the syntax used by the SQL templates. This approach often needs hard coding for every parameter to be extracted.

For example, a new parameter is created by user and is given the name NEW_PROPERTY. If you want to access it by using </NEW_PROPERTY/>, the below code is needed.

else if (paramName.equals("NEW_PROPERTY")) {
         List<Map<String, String>> newPropertyTableValue = (List<Map<String, String>>)
         ElementParameterParser.getObjectValue(node, "__NEW_PROPERTY__");
         for (int ii = 0; ii <newPropertyTableValue.size(); ii++) {  
         Map<String, String> newPropertyMap =newPropertyTableValue.get(ii);
         realValue += ...;//append generated codes
         ......
         }
         }

• The EXTRACT(__GROUPBY__); approach:

The below code shows the second way to access the tabular parameter (GROUPBY).

<%
         String query = "insert into " + __TABLE_NAME__ + "(id, name, date_birth) select sum(id), name, date_birth from cust_teradata group by";
         EXTRACT(__GROUPBY__);
         for (int i=0; i < __GROUPBY_LENGTH__ ; i++) {
         query += (__GROUPBY_INPUT_COLUMN__[i] + " ");
         }
         %>
         <%=query %>;

When coding the statements, respect the rules as follows:

• The extract statement must use EXTRACT(__GROUPBY__);. Upcase should be used and no space char is allowed. This statement should be used between <% and %>.

• Use __GROUPBY_LENGTH__, in which the parameter name is followed by _LENGTH, to get the line number of the tabular GROUPBY parameters you define in the Groupby area on a Component view. It can be used between <% and %> or <%= and %>.

• Use code like __GROUPBY_INPUT_COLUMN__[i] to extract the parameter values. This can be used between <% and %> or between <%= and %>.

• In order to access the parameter correctly, do not use the identical name prefix for several parameters. For example in the component, avoid to define two parameters with the names PARAMETER_NAME and PARAMETER_NAME_2, as the same prefix in the names causes erroneous code generation.