Welcome to part 2 of the 2 part series on Advanced List Page (ALP) queries. In the first part( ()), I covered the functional aspects of ALP queries and I hope you had chance to read it. In this blog, let's take a look at the technical details.
Converting a custom List Page query to ALP query
Now let us take a deep dive and look at what it takes to convert a custom list page query to a ALP query.
ALP tokens and Named Parameters
Before making changes to the list page query it is important to understand new ALP related query tokens and Named Parameters in Sourcing 7.0.
Named Parameters – In earlier releases of Sourcing, the question mark (?) is the placeholder for the filter parameter in the WHERE clause. These parameters were positional and the order and the type should match the order and the type defined in the Filter Prompts tab. In Sourcing 7.0 with the introduction of Named Parameters, it is easier manage the query as the constraint of matching the number, order does not apply. The Named Parameter needs to be defined only once in the Filter Parameter tab and it can be used multiple times in the query.
</p><p>*<%DYN_JOIN%>
- This token is mandatory for all ALP queries. It adds JOIN conditions for tables that are dynamically added for display. For example, the end user might want to add an extension field to the query output and also filter by the extension field. By adding this token, the system automatically adds the JOIN conditions.</p><p><%DYN_FILTER%> - This token is mandatory for all ALP queries. It adds JOIN conditions for tables that are dynamically added for filtering.</p><p>
<%AND(sql expression and parameter name)%>* - This token is used for filter parameters that are mandatory. The SQL expression and the named filter parameter are passed. </p><p> Usage:</p><p> <%AND(“UPPER(T1.DISPLAY_NAME) LIKE <%(?( T1.DISPLAY_NAME)%>”)%></p><p>
<%AND_OP(sql expression, <%?(parameter name)%>)%> - This token is used for filter parameters that are optional. The sql expression is typically something simple that compose a filter condition. Parameter name is the name of the parameter that is defined in the filter prompts tab of the query definition. If there are no values for the attribute, it will be removed from the WHERE clause. If the AND_OP token is used, the Optional flag on the Filter Parameter should be set to true.
</p><p> Usage:</p><p> <%AND_OP(“UPPER(T1.DISPLAY_NAME)”, <%?( T1.DISPLAY_NAME)%>)%></p>h2. How to convert a List Page Query to ALP query?
<p>After upgrading to Sourcing 7.0, the custom list page queries will continue to work, however to take advantage of the new ALP features the query needs to be updated. </p><p>Let us start with a simple query which allows searching projects by name.</p><p>
+SELECT </p><p> <%RESULTS%> </p><p>FROM </p><p> <%SCHEMA%>.FCI_PRO_PROJECTS T1 </p><p> LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T2 </p><p> ON (T1.STATUS_OBJECT_ID=T2.OBJECTID) </p><p> INNER JOIN <%EXT_TABLE(projects.projects)%> T3 </p><p> ON T1.OBJECTID = T3.PARENT_OBJECT_ID </p><p>WHERE </p><p> T1.CONTEXTID=<%CONTEXT(projects.projects)%> AND </p><p> T1.INACTIVE = 0 AND T1.IS_TEMPLATE=0 AND </p><p> UPPER(T1.DISPLAY_NAME) LIKE ? </p><p>
<%ORDERBY%>+</p><p> </p><p>To convert the above query to a ALP query requires a number of changes to the query. Let me explain what they are:</p>h4. Step 1: Add <%DYN_JOIN%> token
<p>This is the first step. This token should be placed after the objects table name and alias. In the example above, the token should be placed after T1 which is the table alias for FCI_PRO_PROJECTS table. Note, the system requires the table alias to be T1. Using a different table alias will result in error.</p>h4. Step 2: Add <%DYN_FILTER%> token
<p>This token should be placed in the WHERE clause. At least one condition should be before the <%DYN_FILTER%> token because the first term in the resolved token will be an AND. </p><p>Both DYN_JOIN and DYN_FILTER tokens have to be in the SQL. Absence of one of the tokens will result in a hard error when trying to save the query. </p>h4. Step 3: Add new result field/update existing result field
<p>In Sourcing 7.0 a new field called Column ID is added to the Query Results page. The purpose of this field is to provide ability to the system to uniquely identify a column. It is recommended that the value of the column ID field is set to TableAlias.COLUMN_NAME. For example, if the project owner field is added as a new column to the query, the value of the Column ID field can be set to T1.DOC_OWNER_USER_OBJECT_NAME. So in most cases the value of the Database Column Name and the Column ID fields will be the same. In situations where the columns are repeated in a query, the Database Column Name will be the same, so a UpperCamelCase format, for example, ProjectOwner can be used for the Column ID</p><p>Note that on a system upgraded to Sourcing 7.0, the Column ID value will be initialized with the UNIQUE_DOC_NAME value as part of the upgrade. Whenever a custom query is edited, it is recommended that the UNIQUE_DOC_NAME is replaced with a meaningful value such as TableAlias.COLUMN_NAME or UpperCamelCase name.</p><p> <img height='235' alt='image' width='700' src='https://weblogs.sdn.sap.com/weblogs/images/252173886/image008.png' border='0'/></p>h4. Step 4: Update filters to use Named Parameters
<p>
Once the DYN filters are added to the query, it is no longer possible to use the old filter syntax using “?”. The filters conditions in the WHERE clause should be updated to use the new named filter using the AND token. In the example, the name filter is a required filter and is named as T1.UNIQUE_DOC_NAME in the Filter Prompts tab of the query definition. Use the AND token as shown below
<%AND("UPPER(T1.DISPLAY_NAME) LIKE <%?(+ T1.DISPLAY_NAME+)%>")%>
It is important to note, for all “required” filters, the Fixed Operator flag on the filter should be checked as shown below.
!https://weblogs.sdn.sap.com/weblogs/images/252173886/image009.png|height=365|alt=image|width=465|src...!
Converted Query
The query below is a converted ALP query.
+SELECT </p><p> <%RESULTS%> </p><p>FROM </p><p> <%SCHEMA%>.FCI_PRO_PROJECTS T1 <%DYN_JOIN%></p><p> LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T2 </p><p> ON (T1.STATUS_OBJECT_ID=T2.OBJECTID) </p><p> INNER JOIN <%EXT_TABLE(projects.projects)%> T3 </p><p> ON T1.OBJECTID = T3.PARENT_OBJECT_ID </p><p>WHERE </p><p> T1.CONTEXTID=<%CONTEXT(projects.projects)%> AND </p><p> T1.INACTIVE = 0 AND T1.IS_TEMPLATE=0 </p><p> *<%AND_OP("UPPER(T1.DISPLAY_NAME)",<%?(T1.DISPLAY_NAME)%>)%> *</p><p> <%DYN_FILTER%></p><p>
<%ORDERBY%>+</p><p> </p><p>That is it. Now let us see the output of the query.</p><p>The screenshot below shows the List page query without DYN_JOIN token</p><p> </p><p> !https://weblogs.sdn.sap.com/weblogs/images/252173886/image010.png|height=229|alt=image|width=671|src...!</p><p>The screenshot below shows a converted query that uses DYN_JOIN tokens. Note the Personalize icon that allows personalizing the query and the Add button that allows adding new filters are enabled.</p><p> !https://weblogs.sdn.sap.com/weblogs/images/252173886/image011.png|height=209|alt=image|width=700|src...!</body>
