Description

In some cases, a List action does not return all the fields that you require in your report.
If the required fields are available from another List action, you can create a new report with the combined data.
Multiple ways to do this include to:

1. Use a program script with logic to combine the results after running the separate actions in the script.
2. Use CSV Command Line Interface (CLI) actions like convertCsv or copyCsv to manipulate the data. You might  want also to use a program script.
3. Use a simple SQL database query to get exactly the report you want - this is what we discuss here.

In this article, we discuss using a SQL database query to specify the fields for your report.

Prerequisites 

To create the SQL query, we assume that you have:

 1. Installed ACLI 10.4 or higher.
     Support  for List actions is available in previous versions, however, the last part of the example uses a new action getSqlResultList with Release Notes 10.4.

 2. Created a table and query access to a database like Postgres, MySql, MS SQL Server, Oracle, or similar.
     ACLI has a built-in Postgres JDBC driver, the example uses Postgres.
     If you use another database, configure the dbJar parameter to point to the available JDBC driver for your database either by URL or adding to your libs/jdbc directory in the ACLI installation.

Details

You need to configure your database access information in your acli.properties ACLI configuration similar to how you configure site access.
For example, you can:

...