About IDO Derived Property SQL Expressions

A derived property in Mongoose is a property whose value is computed by a SQL expression at runtime, it is not stored in a database column. The SQL expression for the computation is entered on the Implementation tab of the IDO Properties form. You must check out the IDO on the IDOs form. Click the New Property button and create a Derived Property.

The IDO runtime injects the expression directly into the Select clause of the generated query from the IDO. Understanding the syntax requirements and performance implications is essential to create effective derived properties.

You can use the derived property for faster development creating inline expressions executed as sub-queries in IDO calls within Mongoose. Because it is a part of the IDO infrastructure it is more accessible for development, within the Mongoose application in the IDO. Not requiring direct access to the database behind the application allows for this development to occur in the Infor Cloud Environments where Infor maintains the database and all the security that is necessary for access.

Syntax requirements

  • Must be a valid SQL expression - The expression is injected directly into the SELECT clause of the generated query. Any SQL syntax error causes the entire IDO load to fail. Test the SQL syntax in SQL Server Management Studio before trying to use in Mongoose.
  • Must return a single scalar value - No multi-row results; the expression becomes one column in the SELECT. If you see the error Sub-query returned more than one row, a derived property expression is likely returning multiple rows. Use SELECT TOP 1 in sub-queries to prevent this.
  • Reference table columns using the table alias - The IDOs base table uses an alias, typically t0, t1, etc. Reference columns with the alias prefix. The alias is defined on the IDO Tables form for a given table in an IDO.
  • Use parentheses around sub-queries - Wrap any sub-query in parentheses, for example: (SELECT TOP1 col FROM table WHERE ...).
  • Must match the declared data type - If the property is declared as INT, the expression must return an integer-compatible value. Mismatched types cause runtime conversion errors.
  • No trailing semicolons - Te expression is embedded in a larger query. A semicolon would terminate the query prematurely.
  • No GO or batch separators - The expression is a single inline expression, not a batch script.
  • If using SELECT TOP 1 in your derived property it is best practice to have an ORDER BY on the query. For example SELECT TOP 1 FROM table view ORDER BY column1. The ORDER BY column, if possible, should be an indexed column.

Formatting tips for syntax used in Derived property on the Implementation tab of an IDO Derived Property

Simple column calculation

The implementation syntax below could be used for a DerExtCost derived property. The table alias t0 is specified on the IDO Tables form:

t0.Qty * t0.UnitCost

CASE expression

CASE WHEN t0.Status = 'A' THEN 1 ELSE 0 END

Correlated sub-query

Always use TOP 1 to guarantee a single value is returned:

(SELECT TOP 1 Name FROM custaddr WHERE cust_num = t0.cust_num AND cust_seq =  
0)

SQL function call

ISNULL(t0.Description, N'')

Concatenation

Combine multiple columns into a single string value:

t0.FirstName + N' ' + t0.LastName

CAST to a different format

CAST(t0.Amount AS DECIMAL(18,2))

Common pitfalls

Pitfall Explanation
Using column aliases in the expression The alias is applied by the IDO automatically using the property name. Do not add your own.
Referencing other derived properties Reference only base table columns, not other derived properties.
Using SELECT * Only scalar expressions are allowed. Do not use SELECT *.
Missing Unicode prefix on string literals Use N'...' for string literals to ensure nvarchar compatibility.
Not handling NULL values Use ISNULL() or COALESCE() if NULL values could cause issues downstream.

Table alias reference

The base table of the IDO typically gets the alias t0. Joined tables get t1, t2, etc. Check the IDO's table or view definition on the IDO Tables form to confirm which alias corresponds to which table.

Performance considerations

Aggregate expressions in sub-queries

Using aggregate functions (COUNT, SUM, MIN, MAX, etc.) in sub-queries within a derived property expressions can be expensive. The derived expression executes for every row returned by the IDO's base query.

Before using an aggregate sub-query, consider these questions:
  • How many records are in the table being aggregate? - Summing a handful of records per row is acceptable. Aggregating across tens of thousands or hundreds of thousands of records per row is not recommended.
  • How many records are in the IDO's base table? - The aggregate expression runs once for each row in the base result set. If the base table returns 1000 rows and each row's sub-query aggregates 10,000 records, which is a 10 million row evaluations.

Aggregate expressions are appropriate when the sub-query processes some records, for example, order lines for a single record. Then should be avoided when aggregated table contains tens of thousands of records or more per evaluation

Avoid COALESCE in WHERE clauses

Use COALESCE in the SELECT portion of a derived expression is acceptable.

(SELECT COALESCE(description, 'no description') FROM dbo.sometable WHERE id =t0.id)

However, using COALESCE in a WHERE clause prevents index usage and causes full table scans.

This is a sample of a problem that causes poor performance:

(SELECT column1 FROM dbo.sometable WHERE COALESCE(xyz, 'x') = t0.somecolumn)
The optimizer cannot use indexes when a function wraps the column being filtered. Instead, rewrite the WHERE clause to avoid wrapping the column. This is a better alternative: 
(SELECT column1 FROM dbo.sometable WHERE (xyz = t0.somecolumn OR (xyz IS NULL AND 'x' = t0.somecolumn)))

Troubleshooting

Error Cause Fix
"Sub-query returned more than one row" Sub-query returns multiple rows Add TOP 1 to the sub-query or add a more specific WHERE clause
Data type conversion error Expression returns a type that does not match the property's declared type. Add CAST() or CONVERT() to match the declared type.
Invalid column name Table alias is incorrect or column does not exist Verify the alias on the IDO Tables form and the column name in the view or table.
Query time-out Expensive aggregate or poorly filtered sub-query Reduce the data set, add a better filtering, or avoid aggregates on large tables.