In this fourth part of the series we will unravel the mystery surrounding the SELECT statement.
Additional useful information
Unfortunately, some parts of the help documentation are out of date. Many functional enhancements are not properly covered. Incomplete and confusing documentation makes it difficult to see the possibilities offered by Script Logic. Admittedly, most improvements have been documented in the Notes which can be found at SAP Marketplace but locating the information can be difficult. Sometimes different sources contain contradicting statements. I have collected updated information and added my personal findings about the statements covered in this article. If in doubt, please check the official SAP documentation to ascertain the support status of any features you plan to use.
Syntax of the SELECT statement
Throughout this article I have written SELECT statements without brackets or quotation marks such as below.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE = PAYR)
Most Script Logic examples use a more complex syntax. See the statement below for comparison.
*SELECT(%AUDITSRC%, "[ID]", AUDITID, "[SRCTYPE] = 'PAYR'")
I find the version with fewer special characters more readable and it seems to work exactly the same way. It is also much easier to write. If there are many conditions, quotation marks might improve readability. Anyway, the system doesn’t seem to care either way so it’s your call.
Variables with the SELECT statement
Although it’s not easy to find documentation, the SELECT statement does allow the use of variables. Both DM variables (the ones with $ signs around them) and % variables can be used. Let’s have a look at a pair of SELECT statements we used before.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE= PAYR)
*SELECT(%INCPERC%, INCPERC, AUDITID, SRCTYPE= PAYR)
This could be rewritten as follows.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE= PAYR)
*SELECT(%INCPERC%, INCPERC, AUDITID, ID = %AUDITSRC%)
Keyword AND with the SELECT statement
According to the help documentation the SELECT statement does not support multiple selection criteria using AND or OR keywords. Documentation is in need of an update as keyword AND is supported. The statement below is an example of a valid SELECT statement with multiple criteria.
*SELECT(%AUDITSRC%, ID, AUDITID, IS_CONSOL = Y AND IS_CONVERTED = Y AND CALC = N)
Operators with the SELECT statement
According to the help documentation only operators = and <> can be used with the SELECT statement. Again, the documentation does not list the many enhancements. In fact, the SELECT statement supports comparisons with the following operators: =, <>, <, >, <=, >=. For example, the following statement can be used to select AUDITID’s with property SRCTYPE values PAYR01 to PAYR10 with the exception of member ID “BONUS”.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE >= PAYR01 AND SCRCTYPE <= PAYR10 AND ID <> BONUS)
Logical OR with the SELECT statement
The system also allows something akin to logical OR as it accepts multiple values in the condition. The statement below dynamically selects AUDITID members where property SRCTYPE is equal to PAYR or MANUF. This is not quite the same as the keyword OR but it is better than advertised for in the help documentation.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE= PAYR, MANUF)
A word of caution is warranted. The following statement behaves exactly the same way as the one above. I find this is counterintuitive as I would expect no members to be selected in this case.
*SELECT(%AUDITSRC%, ID, AUDITID, SRCTYPE = PAYR AND SRCTYPE = MANUF)
Naturally, you can also use the keywords XDIM_ADDMEMBERSET and XDIM_FILTER to fine-tune your selection. Here’s an example. This is one of the few cases where the system requires quotation marks.
*XDIM_FILTER ENTITY = [AUDITID].properties("SRCTYPE") = PAYR
I have experienced problems with XDIM_FILTER but these would probably be fixed by applying the relevant correction notes. In any case, I prefer using SELECT statement variables whenever possible and I have had little use for XDIM_FILTER.
In the fifth part of this series, we will set the record straight as far as XDIM_MEMBERSET is concerned.
