PROP:GroupBy, PROP:Having

Top  Previous  Next

The SQL view engine supports PROP:GroupBy and PROP:Having. These properties allow you to add respectively GROUP BY and HAVING clauses to your SELECT statement. Note that PROP:GroupBy must be set first to allow PROP:Having to be generated.

 

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.