computeSqlValueRange-Funktion

Die nachfolgenden Inhalte sind nur in englischer Sprache verfügbar.

Purpose

The computeSqlValueRange function allows for the integration of custom SQL expressions directly into the SELECT statements that PLANTA generates. It is primarily used to append calculated or dynamic columns to the system-generated queries during module loading.

PLANTA passes on the SQL alias of the currently queried data table (dt_name) as a parameter. The function must return a valid SQL expression or scalar subquery, which will then be injected into the SELECT clause as a virtual column.

How It Works

1. When a user opens a module, PLANTA determines which data to load based on the module customizing.

2. One or multiple SQL queries are constructed using the non-virtual data items defined in the customizing.

   For example, assume a module with a single data area from DT461 (Project), with DI001001 (Project ID) customized.
   The server will generate a query like this:

   SQL

   SELECT
     DT461_L0.DI001001
   FROM DT461 DT461_L0
   

3. If a virtual data item with a computeSqlValueRange is added, the function is evaluated at query generation time.

4. The return value is appended to the SELECT clause as an additional virtual column.

   SQL

   SELECT
     DT461_L0.DI001001,
     (<computeSqlValueRange_return_value>) AS DI100000
   FROM DT461 DT461_L0
   

Important Notes

• The returned expression must be SQL-compatible and syntactically correct for inclusion in a SELECT clause.

• The returned value is not a full query — it's interpreted as an inline expression or scalar subselect (e.g., alias.column or (SELECT ...)).

• Use the provided table alias (dt_name) to ensure proper referencing of fields in the current data context.

Best Practices

• Always validate your SQL expression in a dedicated SQL tool before integrating it into computeSqlValueRange.

Example: Scalar Subselect

In this example, we define a computeSqlValueRange that returns a scalar subselect.
The function is customized on DT461 (Project) and calculates the number of tasks (DT463) linked to the project via the project ID foreign key (DT463.DI001097 - DT461.DI001001).

def computeSqlValueRange(dt_name):
    return f"""
            SELECT COUNT(DI001097)
            FROM DT463
            WHERE DI001097 = {dt_name}.DI001001
    """

Using the alias in the sub-select makes sure we get the task count for the respective projects present in the module and not for every project in the system.

Brackets will be added automatically when the string is inserted into the SQL query!

Resulting SQL (Simplified)

Assuming dt_name = 'DT461_L0', the query becomes:

SELECT
  DT461_L0.DI001001,
  (
    SELECT COUNT(DI001097)
    FROM DT463
    WHERE DI001097 = DT461_L0.DI001001
  ) AS DI100000
FROM DT461 DT461_L0

When to Use

Use computeSqlValueRange when you need to:

• Add calculated fields without altering the base table schema.

• Count or aggregate related records (e.g., children, references).

• Display dynamic field values based on runtime conditions or related data.

Common Pitfalls