How to receive a string parameter from stored() to a macro

[andre.martins]

I'm working on a query to calculate a frequency of a field inside a dataset. So first of all I coded a simple function that works for one hard coded field and then and made a function macro that should receive a string parameter containing the field name and calculate its frequency.

Code: Select all

EXPORT test(Field) := FUNCTIONMACRO
  Layout := RECORD
    UNSIGNED1 PersonID;
    STRING15  FirstName;
    STRING25  LastName;
  END;

  students := DATASET(
      [
        {1,'Fred','Smith'},
        {2,'Joe','Blow'},
        {3,'Jane','Smith'}
      ],
      Layout);

  r := RECORD
    students.#EXPAND(Field);
    UNSIGNED Frequency := COUNT(GROUP);
  END;

  final_result := TABLE(students, r, Field);
  sort_result := SORT(final_result, Field);
  RETURN OUTPUT(sort_result);
ENDMACRO;


For running it on thor works normally calling test('LastName'). But I'm trying to receive this string value from the user using a STORED() call as the following example:

Code: Select all

IMPORT ^.functions AS F;

EXPORT SVC5_Test() := FUNCTION
  STRING Field := 'LastName' : STORED('Field'); // Receive LastName, FirstName or PersonID

  result := F.test(Field);

  RETURN result;
END;


In this way, I stucked on this following error in the line with #EXPAND():

2071: Constant expression expected

In short, is there a way to receive this field as a string parameter and send it to a function macro (or any other way with template language, for example), performing and publishing this query based on the parameter field sent by the user?

While the development of this code I was thinking about the possibility of being able to perform a query only adding a new field inside the record structure, without the need to change the roxie query logic, in order to work dinamically based on the parameter/field sent by the user. Keep in mind that the real RECORD has more than 50 fields avaiable that I'd group and calculate its frequency.

[rtaylor]

andre,

Unfortunately, I have no good answer for you. The Template Language (which #EXPAND is part of) was originally designed to receive an XML string from an external app (like a website), generate ECL code from that XML input, run it, and return the results. That means it is expecting a string constant and you're trying to pass in a variable-value parameter.

I've been trying for years to figure out a good workaround to do all that within the HPCC Systems environment (you are not the first to ask). The best solution I've had so far has been to make it a two-step manual process: first step to generate the XML, then copy that and paste it into the code to run the actual job. Even if I use the "drilldown" mechanism in the ECL IDE, it's still a two-step manual process.

Contact me directly on Teams if you want to have a discussion about all the possible ways I've already tried (many) and your specific requirements for this task.

HTH,

Richard