Setting the Extraction Condition¶

Extraction condition can be set on “Edit Query” screen and “Edit Data Reference” screen of aggregate list.

“Search Settings” of aggregate list is a functionality that aims at quick retrieval of information for the user who is browsing the data reference.

“Extraction Condition” is a functionality that refines the information beforehand at administrator side which is not to be displayed to the user.

• “Extraction Condition List” tab of query

Note

If multiple extraction conditions are set, the search is carried out with default “AND condition”.

“OR condition” can also be speficied by selecting “OR search”.

• “Extraction Condition” of data reference (aggregate list)

Warning

OR search cannot be set in “Extraction condition” of aggregate list.

Note

The structure of SQL executed at the time of browsing data reference is as follows.

SELECT

    [Column list displayed on Edit Data reference screen]

FROM

   ( SELECT

       [Column list added to the query]

     FROM

       [Table added in query]

     WHERE

       [Extraction condition set in query]

   ) IM__QUERY__

WHERE

   [Extraction condition set in data reference]

As far as possible, data should be refined at the query stage to speed-up the SQL execution. However, it depends on the database implementation.

If multiple data references are created by sharing a single query, setting extraction condition for each data reference is better in some cases.

Carry out appropriate settings based on the usage.

• Dynamic Parameters

Parameter name	Generated value
<%DYNAMIC_PARAMETER%>	This is used when parameter value is set through API. Data reference cannot be created from a query which contains even a single extraction condition for which this parameter is set. For details, refer jp.co.intra_mart.foundation.viewcreator.model.Query#execute.
<%ENCODING%>	Encoding character string associated with login user at the time of SQL execution, is set.
<%LOCALE%>	Locale character string associated with login user at the time of SQL execution, is set.
<%NOT_NULL%>	IS NOT NULL is set. ※ When this parameter is selected, select “Exactly matching” in “Extraction method” without fail.
<%NULL%>	IS NULL is set. ※ When this parameter is selected, select “Exactly matching” in “Extraction method” without fail.
<%SYSTEM_DATE(*)%>	System date at the time of SQL execution is set. Character string is set as per the format wherein data of system date is specified. Date data generated in character strings is passed to type conversion function prepared for each database. ※This parameter should be set in date format or time stamp format.
<%USER_ID%>	Login user code at the time of SQL execution is set.

Note

• <%SYSTEM_DATE%> can be used to specify time of previous day, an hour before etc..

Parameter <%SYSTEM_DATE(yyyy/MM/dd HH:mm)%> of system date is set at the time of execution.

However, + operator or - operator can be used in format character string part for description.

• For setting 1 hour before the system date

<%SYSTEM_DATE(yyyy/MM/dd HH-1:mm)%>

• For setting date previous to the system date

<%SYSTEM_DATE(yyyy/MM/dd-1 HH:mm)%>

※ Only + and - can be used in operators.

※ Do not enter space before or after the operator.

• Direct input

• Additional direct input

Note

Dynamic parameter can be used for direct input as well.

Warning

In case of direct input, one needs to take precautions to ensure that tables or records are not corrupted.

Note

Original dynamic parameter can be created by editing the following files.

WEB-INF/conf/viewcreator-config.xml

<param>
  <param-name> Parameter Name </param-name>
  <class-name>jp.co.intra_mart.foundation.viewcreator.params.SystemParameter***</class-name>
</param>

Use only single byte capital letters and underscore in parameter name (value of <param-name> tag).

Specify implementation class of jp.co.intra_mart.foundation.viewcreator.SystemParameter interface in value of <class-name>.

E.g.)

public class SystemParameterLocale implements SystemParameter {

   @Override
   public String get(final String syskey) {
      final AccountContext context = Contexts.get(AccountContext.class);
      return context.getLocale().getLanguage();
   }
}

Note

Restart the server to reflect the changes.