# 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. Information noteNote: There is no exception to the lines in the middle part of a SQL statement or within the <%... %> syntax. ## 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. Information noteNote: Parameters that the SQL templates can access with this syntax are simple. They are often used for connection purpose and can be easily defined in components, such as TABLE\_NAME, DB\_VERSION, SCHEMA\_TYPE, etc. ## 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?](https://help.qlik.com/talend/en-US/studio-user-guide/8.0/what-is-job-design). For more information on the <%...%> syntax, see [The <%...%> syntax](https://help.qlik.com/talend/en-US/studio-user-guide/8.0/sql-template-writing-rules#the---...---syntax_r). For more information on the </.../> syntax, see the following section. Information noteNote: Parameters that the SQL templates can access with this syntax are simple. They are often used for connection purpose and can be easily defined in components, such as TABLE\_NAME, DB\_VERSION, SCHEMA\_TYPE, etc. ## 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_; ``` The statement identifies the TABLE\_NAME parameter and then removes the corresponding table. For more information on the <%...%> syntax, see [The <%...%> syntax](https://help.qlik.com/talend/en-US/studio-user-guide/8.0/sql-template-writing-rules#the---...---syntax_r). For more information on the <%=...%> syntax, see [The <%=...%> syntax](https://help.qlik.com/talend/en-US/studio-user-guide/8.0/sql-template-writing-rules#the----...---syntax_r). The following sections present more specific code used to access more complicated parameters. Information noteNote: Parameters that the SQL templates can access with this syntax are simple. They are often used for connection purpose and can be easily defined in components, such as TABLE\_NAME, DB\_VERSION, SCHEMA\_TYPE, etc. ## 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. Information noteWarning: Make sure that the name you give to the schema parameter does not conflict with any name of other parameters. For more information on component schema, see [Basic settings tab](https://help.qlik.com/talend/en-US/studio-user-guide/8.0/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> newPropertyTableValue = (List>) ElementParameterParser.getObjectValue(node, "__NEW_PROPERTY__"); for (int ii = 0; ii 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.