Access dynamic field name in ESQL

Question

I have a requirement to create and access dynamic field names using ESQL. Please suggest your inputs.

The source of the field dynamicFieldName in the below function comes from a table.

CREATE FUNCTION getFieldValue(IN Environment REFERENCE, IN userId CHARACTER, IN dynamicFieldName CHARACTER) RETURNS CHARACTER
BEGIN
   RETURN THE (SELECT ITEM FIELDVALUE(ref.dynamicFieldName) FROM Environment.Variables.dbData[] AS ref where ref.USER_ID = userId);
END;

This function is throwing error

BIP2492E: Illegal type parameter '1' of the function 'FIELDVALUE'. A non-list field reference is required.

Answer 1

The ESQL field reference overview explains your requirement:

Because the names of the fields appear in the ESQL program, they must be known when the program is written. This limitation can be avoided by using the alternative syntax that uses braces ( { ... } ). This syntax allows you to use any expression that returns a non-null value of type character.

So your code should look like this:

FIELDVALUE(ref.{dynamicFieldName})