如何为SQL查询添加参数？Maximo与Birt动态参数配置咨询

Absolutely, you can pull the wonum value from Maximo's built-in params["where"] and assign it to your custom :Param in BIRT—here are a few practical approaches to make this work:

Approach 1: Parse params["where"] via BIRT Dataset Scripting #

This is the most flexible method, especially if you need to handle varying formats of the Maximo where clause.

1. First, create a custom string parameter in your BIRT report (name it Param to match your SQL placeholder).
2. Open your dataset's Script Editor and navigate to the beforeOpen event.
3. Add this JavaScript code to extract the wonum value and assign it to your parameter:

// Grab the Maximo-generated where clause
const maximoWhereClause = params["where"];

// Use regex to extract the wonum value (handles quoted strings like wonum='1234')
const wonumMatch = maximoWhereClause.match(/wonum\s*=\s*'([^']+)'/i);

if (wonumMatch) {
    // Assign the extracted wonum to your custom parameter
    reportContext.setParameterValue("Param", wonumMatch[1]);
} else {
    // Handle cases where wonum isn't in the where clause (set default or skip)
    reportContext.setParameterValue("Param", "");
}

4. Now your SQL query can safely use :Param—BIRT will substitute the extracted wonum value at runtime.

Approach 2: Direct Database String Manipulation (Less Flexible) #

If you prefer to handle everything in SQL (no BIRT scripting), you can use your database's string functions to parse the params["where"] value directly. For example, in Oracle:

SELECT * 
FROM workorder a 
WHERE &params["where"] 
AND a.parent = (
    SELECT SUBSTR(
        &params["where"], 
        INSTR(&params["where"], '''') + 1, 
        INSTR(&params["where"], '''', 2) - INSTR(&params["where"], '''') - 1
    ) FROM DUAL
)

⚠️ Note: This method only works if params["where"] strictly follows the format wonum='1234'. If the where clause includes multiple conditions or different formatting (e.g., unquoted numbers, IN clauses), this will break.

Approach 3: Pass wonum Directly from Maximo (Cleanest for Integrated Reports) #

If you're running the report directly from Maximo (not standalone BIRT), you can skip parsing entirely by configuring Maximo to pass the wonum as an explicit parameter:

• In Maximo's Report Administration, open your report configuration.
• Add a new parameter mapping: map your BIRT Param parameter to Maximo's &wonum variable.
• When users run the report from a work order record, Maximo will automatically pass the current wonum to BIRT, no parsing needed.

Key Notes #

• Always test edge cases: what if params["where"] doesn't include wonum? Or has multiple wonum conditions? Adjust your regex or script logic to handle these scenarios.
• For regex flexibility, you can modify the pattern to handle unquoted numeric wonum values: /wonum\s*=\s*(\d+)/i

内容的提问来源于stack exchange，提问作者Frederic Cantenot