skip to content
Clarion Community Help
User Tools
Log In
Site Tools
Search
Tools
Show page
Old revisions
Backlinks
Recent Changes
Media Manager
Sitemap
Log In
>
Recent Changes
Media Manager
Sitemap
Trace:
view_support_for_aggregate_functions.htm
| **Navigation:** SQL Drivers >====== VIEW support for aggregate functions ====== | [[runtime sql properties for views using sql drivers.htm|{{btn_prev_n.gif|Previous page}}]][[coming future.htm|{{btn_home_n.gif|Return to chapter overview}}]][[debugging your sql application.htm|{{btn_next_n.gif|Next 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|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.htm|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
Page Tools
Show page
Old revisions
Backlinks
Back to top