Using aggregate functions, groupBy clauses, and having clauses
For example: If the application contains a table of ticket IDs, ticket statuses, and support engineer names, you could use the COUNT aggregate to generate an SQL query that would count and return the open tickets assigned to each support engineer (agent):
To further refine the results of this query, you could also add a HAVING clause. The having parameter can also take an aggregate function.
For example: To return records for only those support engineers who have more than ten open tickets, you would add a HAVING clause with a COUNT function:
C example and exercise
The sample code in this example will generate a SELECT query using the COUNT aggregate function with GROUP BY and HAVING clauses.
The sample code queries the field_dispprop form, an AR System Metadata form that stores information about the display properties for fields and forms, to determine the number of records in which data is stored as a Character Large Object (CLOB). For each distinct schema and view, this query determines the total number of display properties and the number of non-NULL short display properties. You could then subtract the short property count from the total to get the number of display properties that are stored as CLOBs rather than as regular text fields in field_dispprop.
Here is the sample code:
buildFieldId(ARMultiSchemaFieldIdStruct *ff,
const char *a, ARInternalId fieldId)
{
strcpy(ff->queryFromAlias, a);
ff->fieldId = fieldId;
}
}
static void
buildFieldFunc(ARMultiSchemaFieldFuncStruct *ff,
const char *a, ARInternalId fieldId, int funcId)
{
strcpy(ff->queryFromAlias, a);
ff->fieldId = fieldId;
ff->funcId = funcId;
}
}
int
arschematest(ARControlStruct *ctrl, ARStatusList *status)
{
int result = AR_RETURN_OK;
static const char field_disppropName[]="AR System Metadata: field_dispprop";
static const char field_disppropAlias[] = "field_dispprop";
enum {
F_recordId = 1,
F_vuiId = 20017,
F_schemaId = 20018,
F_propShort = 20504
};
ARMultiSchemaFuncQueryFromList fromList = {0,};
ARMultiSchemaFuncQueryFromStruct fromItem[1];
unsigned int numMatches = 0, *numMatchesPtr = &numMatches;
unsigned int numMatches = 0, *numMatchesPtr = &numMatches;
/*
* SELECT f.schemaId, f.vuiId, COUNT(f.recordId), COUNT(f.propShort),
* FROM field_dispprop f
* GROUP BY f.schemaId, f.vuiId
* HAVING COUNT(f.propShort) < COUNT(f.recordId)
*/
fromItem[0].type = AR_MULTI_SCHEMA_SCHEMA_NAME;
strcpy(fromItem[0].u.schemaName, field_disppropName);
strcpy(fromItem[0].queryFromAlias, field_disppropAlias);
fromItem[0].joinType = 0;
fromItem[0].joinQual = NULL;
fromList.numItems = 1;
fromList.listPtr = fromItem;
fromList.listPtr = fromItem;
ARMultiSchemaFieldFuncStruct fieldId[4];
buildFieldFunc(&fieldId[0], field_disppropAlias,
F_schemaId, AR_MULTI_SCHEMA_FUNC_NONE);
buildFieldFunc(&fieldId[1], field_disppropAlias,
F_vuiId, AR_MULTI_SCHEMA_FUNC_NONE);
buildFieldFunc(&fieldId[2], field_disppropAlias,
F_recordId, AR_MULTI_SCHEMA_FUNC_COUNT);
buildFieldFunc(&fieldId[3], field_disppropAlias,
F_propShort, AR_MULTI_SCHEMA_FUNC_COUNT);
ARMultiSchemaFieldFuncList selectList = {0,};
selectList.numItems = 4;
selectList.listPtr = fieldId;
selectList.listPtr = fieldId;
ARMultiSchemaFieldIdStruct groupBy[2];
ARMultiSchemaFieldIdList groupByList = {2, groupBy};
buildFieldId(&groupBy[0], field_disppropAlias, F_schemaId);
buildFieldId(&groupBy[1], field_disppropAlias, F_vuiId);
buildFieldId(&groupBy[1], field_disppropAlias, F_vuiId);
ARMultiSchemaFuncQualifierStruct having = {AR_COND_OP_NONE, NULL};
ARMultiSchemaFuncRelOpStruct havingRel = {0,};
having.operation = AR_COND_OP_REL_OP;
having.u.relOp = &havingRel;
{
ARMultiSchemaFieldFuncValueOrArithStruct *ffv = &havingRel.operandLeft;
havingRel.operation = AR_REL_OP_LESS;
ffv->tag = AR_FIELD;
buildFieldFunc(&ffv->u.fieldFunc,
field_disppropAlias, F_propShort, AR_MULTI_SCHEMA_FUNC_COUNT);
ffv = &havingRel.operandRight;
ffv->tag = AR_FIELD;
buildFieldFunc(&ffv->u.fieldFunc,
field_disppropAlias, F_recordId, AR_MULTI_SCHEMA_FUNC_COUNT);
}
}
ARMultiSchemaSortList sortList = {0,};
ARMultiSchemaFieldFuncValueListList msEntryList = {0, NULL};
ARMultiSchemaFieldFuncValueListList msEntryList = {0, NULL};
result = ARGetListEntryWithMultiSchemaFields(ctrl, &fromList,
&selectList, NULL, &sortList,
AR_START_WITH_FIRST_ENTRY, AR_NO_MAX_LIST_RETRIEVE,
FALSE, &groupByList, &having, &msEntryList,
numMatchesPtr, status);
return result;
}
The generated SQL query is:
As an exercise, join the AR System Metadata: field_dispprop form with the AR System Metadata: arschema form to retrieve form names instead of or in addition to integer identifiers.