User Tools

Site Tools


view_support_for_aggregate_functions.htm
Navigation:  SQL Drivers >====== VIEW support for aggregate functions ====== Previous pageReturn to chapter overviewNext page

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.

NoteBox.jpg

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.

view_support_for_aggregate_functions.htm.txt · Last modified: 2021/04/15 15:57 by 127.0.0.1