VIEW support for aggregate functions

ide docs
1

This is a pretty neat topic from the help.

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.

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.

2

Also check out the “PROP:Name (SQL Properties)” topic in the help.

v{'EMP:EmpNo',PROP:NAME} = 'count(*)'

or

MyView{'Pre:FieldOne',Prop:Name} = 'top(10) FieldOne'

for example :slight_smile:

1 Like
3

Neat-o. Looks like your line with PROP:Having got “html escaped”.

4

yup sure did. Fixed, thanks!