Table of Contents

Search

  1. Preface
  2. Backing Up and Restoring the Data Vault
  3. Configuring Centera as a Remote Data Vault Store
  4. Configuring Data Archive for a Legacy Source Using Legacy Adapters
  5. Data Archive Seamless Access for PeopleSoft
  6. Data Archive Transaction Restore API
  7. Dropping and Truncating Partitions in Data Archive
  8. High Availability Configuration for the Data Archive and File Archive Service Versions 6.1 to 6.3
  9. 0955-High Availability Configuration for the Data Vault Version 6.4 and Later
  10. How to Create Business Rules to Archive and Purge Transactional Data
  11. How to Uninstall Data Archive 5.1
  12. How to Uninstall Data Archive 5.3
  13. How to Use Scripts to Change Database User Passwords in the ILM Repository
  14. IBM DB2 Database Connectivity and Setup for Data Archive
  15. Installing Data Visualization
  16. Integrating Third-Party Schedulers in ILM Engine
  17. Parallel Processing in Data Archive
  18. Seamless Access Configuration for Siebel Applications
  19. Seamless Access Setup for Oracle E-Business Suite
  20. Using the Data Vault Service JDBC Driver to Connect to the Data Vault
  21. Using Multiple Engines in an ILM Environment
  22. Using PowerExchange ODBC Connections in a Data Archive Retirement Project
  23. Discovering Foreign Key Relationships in Enterprise Data Manager

Data Archive How-To Guide

Data Archive How-To Guide

The INLINE Condition Type

The INLINE Condition Type

The INLINE condition type allows you to evaluate a condition in the driving table without having to run an UPDATE WHERE statement (which is extra processing) or without adding a restrictive Where clause statement on INSERT. For example, our sample rule checks the invoice_status field in the ap_invoice_headers table. The ap_invoice_headers table is also our driving table, which is used to populate the interim table with the list of candidates eligible for archive/purge. We could enforce this business rule as we have already demonstrated with the WHERE or SET condition types. Or we could enforce the business requirement upon insert into the interim table:
INSERT INTO INTERIM A (INVOICE_HEADER_ID) SELECT invoice_header_id FROM ap_invoice_headers H WHERE invoice_date < :p_period_to AND invoice_status=’CLOSED’
This would populate the interim table with all invoice header transactions that had an invoice_status of CLOSED, which meets the business requirement. This also allows us to not have to run an UPDATE statement using the WHERE or SET condition types, thereby eliminating the need for a potentially time-consuming SQL statement. However, this does not identify all of the invoice header transactions that do not have an invoice status of CLOSED. It is useful information to know what transactions failed a rule. By adding business requirements to the WHERE clause of the INSERT statement, the ability to obtain this information is not possible.
However, using the INLINE condition type, we can take advantage of the INSERT statement to meet the business requirement while also providing the exception reporting capability that is critical to Candidate Generation. The INLINE condition type is written as follows:
INSERT INTO INTERIM A (INVOICE_HEADER_ID, INVOICE_NOT_CLOSED) SELECT invoice_header_id , DECODE(H.invoice_status,’CLOSED’,0,1) FROM ap_invoice_headers H WHERE invoice_date < :p_period_to
The INLINE condition type is added to the SELECT statement used to INSERT into the interim table. Like the SET condition type, the INLINE condition type uses a DECODE (or CASE) statement to evaluate the column value and selects a 0 or 1 accordingly. In EDM the INLINE condition is written as:
DECODE(H.invoice_status,’CLOSED’,0,1)
Note that the invoice_status column is aliased with an H. It is good practice to alias the columns in the INLINE in case there are multiple tables in the INSERT FROM clause. This will ensure you are looking at the correct column in the correct table.

0 COMMENTS

We’d like to hear from you!