The SQL view engine supports PROP:GroupBy and PROP:Having. These properties allow you to add respectively GROUP BY and HAVING SQL clauses to your SELECT statement.
PROP:GroupBy must be set first to allow PROP:Having to be generated.
See Also: Runtime SQL Properties for Views using SQL Drivers
Example:
PROGRAM
MAP
END
EMP FILE,DRIVER('ORACLE'),NAME('EMP'),PRE(EMP)
P_EKY_EMP KEY(EMP:EMPNO),NOCASE,OPT,PRIMARY
KEY_DEP KEY(EMP:DEPTNO),DUP,NOCASE,OPT
Record RECORD
EMPNO SHORT !Emp-no
ENAME CSTRING(11) !Employee name
JOB CSTRING(10) !Job
HIREDATE DATE !Hiredate
MGR SHORT !Manager
SAL PDECIMAL(7,2) !Salary
COMM PDECIMAL(7,2) !Commisison
DEPTNO BYTE
END
END
MyView VIEW(EMP)
PROJECT(EMP:Mgr)
PROJECT(EMP:Sal)
END
CODE
OPEN(EMP)
OPEN(MyView)
MyView{'EMP:Sal',PROP:Name} = 'sum(sal)'
MyView{PROP:GroupBy} = 'Mgr'
MyView{PROP:Having} = 'sum(sal) > 100000'
SET(MyView)
NEXT(MyView)
The example code above is the equivalent to “SELECT mgr, sum(sal) FROM EMP GROUP BY mgr HAVING sum(sal) > 100000”
In other words, this code will return a list of all Manager IDs and the total salary of their subordinates if their subordinates make a total of more than 100000.