Function SET189N
********** COMMENT(=======================================================);
********** COMMENT(Process ........: SET_189);
********** COMMENT(Function .......: SET189N);
********** COMMENT(Created on .....: 22/09/00 at 14:43:32);
********** COMMENT(Description ....: SELECT_SQL - SUM, AVG, COUNT MAX, MIN);
********** COMMENT(Version.........: 1);
**********;
********** COMMENT(Full Description: The purpose of this function is to);
********** COMMENT(show how SELECT_SQL can calculate the );
********** COMMENT(SUM, AVG, COUNT MAX, MIN for a field in a file);
**********;
********** COMMENT(Note: To use variables in SELECT_SQL statements see);
********** COMMENT(example SET_010.);
**********;
********** COMMENT(Disclaimer: The following material is supplied as);
********** COMMENT(sample material only. No warranty concerning the);
********** COMMENT(material or its use in any way whatsoever is);
********** COMMENT(expressed or implied.);
**********;
********** COMMENT(Minimum LANSA release: 8.0);
**********;
********** COMMENT(=======================================================);
********** COMMENT(Function control options);
FUNCTION OPTIONS(*DIRECT);
**********;
OPEN USE_OPTION(*ONDEMAND);
********** COMMENT(Group and field definitions);
**********;
DEF_LIST NAME(#S_LSTPSL) FIELDS(#S_189SUM #S_189CNT #S_189AVG #S_189MAX #S_189MIN);
DEFINE FIELD(#S_189TXT1) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC);
DEFINE FIELD(#S_189TXT2) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC);
DEFINE FIELD(#S_189TXT3) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC);
DEFINE FIELD(#S_189TXT4) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC);
**********;
DEFINE FIELD(#S_189SUM) LENGTH(*REFFLD *PLUS 2) REFFLD(#SALARY) COLHDG('Sum');
DEFINE FIELD(#S_189CNT) REFFLD(#SALARY) COLHDG('Count');
DEFINE FIELD(#S_189AVG) REFFLD(#SALARY) COLHDG('Average');
DEFINE FIELD(#S_189MAX) REFFLD(#SALARY) COLHDG('Maximum');
DEFINE FIELD(#S_189MIN) REFFLD(#SALARY) COLHDG('Minimum');
**********;
CHANGE FIELD(#S_189TXT1) TO('''In this function, SELECT_SQL calculates ''');
CHANGE FIELD(#S_189TXT2) TO('''the Sum, Average, Count, Maximum''');
CHANGE FIELD(#S_189TXT3) TO('''and Minimum of all the salaries. ''');
CHANGE FIELD(#S_189TXT4) TO('''This can be done with one SELECT_SQL statement.''');
**********;
BEGIN_LOOP;
CLR_LIST NAMED(#S_LSTPSL);
SELECT_SQL FIELDS((#S_189SUM 'SUM(SALARY)') (#S_189CNT 'COUNT(SALARY)') (#S_189AVG 'AVG(SALARY)') (#S_189MAX 'MAX(SALARY)') (#S_189MIN 'MIN(SALARY)')) FROM_FILES((PSLMST));
ADD_ENTRY TO_LIST(#S_LSTPSL);
ENDSELECT;
**********;
DISPLAY FIELDS(#S_189TXT1 #S_189TXT2 #S_189TXT3 #S_189TXT4) DESIGN(*DOWN) IDENTIFY(*NOID) BROWSELIST(#S_LSTPSL);
END_LOOP;
********** COMMENT(=======================================================);
********** COMMENT(Process ........: SET_189);
********** COMMENT(Function .......: SET189N);
********** COMMENT(Created on .....: 22/09/00 at 14:43:32);
********** COMMENT(Description ....: SELECT_SQL - SUM, AVG, COUNT MAX, MIN);
********** COMMENT(Version.........: 1);
**********;
********** COMMENT(Full Description: The purpose of this function is to);
********** COMMENT(show how SELECT_SQL can calculate the );
********** COMMENT(SUM, AVG, COUNT MAX, MIN for a field in a file);
**********;
********** COMMENT(Note: To use variables in SELECT_SQL statements see);
********** COMMENT(example SET_010.);
**********;
********** COMMENT(Disclaimer: The following material is supplied as);
********** COMMENT(sample material only. No warranty concerning the);
********** COMMENT(material or its use in any way whatsoever is);
********** COMMENT(expressed or implied.);
**********;
********** COMMENT(Minimum LANSA release: 8.0);
**********;
********** COMMENT(=======================================================);
********** COMMENT(Function control options);
FUNCTION OPTIONS(*DIRECT);
**********;
OPEN USE_OPTION(*ONDEMAND);
********** COMMENT(Group and field definitions);
**********;
DEF_LIST NAME(#S_LSTPSL) FIELDS(#S_189SUM #S_189CNT #S_189AVG #S_189MAX #S_189MIN);
DEFINE FIELD(#S_189TXT1) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC);
DEFINE FIELD(#S_189TXT2) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC);
DEFINE FIELD(#S_189TXT3) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC);
DEFINE FIELD(#S_189TXT4) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC);
**********;
DEFINE FIELD(#S_189SUM) LENGTH(*REFFLD *PLUS 2) REFFLD(#SALARY) COLHDG('Sum');
DEFINE FIELD(#S_189CNT) REFFLD(#SALARY) COLHDG('Count');
DEFINE FIELD(#S_189AVG) REFFLD(#SALARY) COLHDG('Average');
DEFINE FIELD(#S_189MAX) REFFLD(#SALARY) COLHDG('Maximum');
DEFINE FIELD(#S_189MIN) REFFLD(#SALARY) COLHDG('Minimum');
**********;
CHANGE FIELD(#S_189TXT1) TO('''In this function, SELECT_SQL calculates ''');
CHANGE FIELD(#S_189TXT2) TO('''the Sum, Average, Count, Maximum''');
CHANGE FIELD(#S_189TXT3) TO('''and Minimum of all the salaries. ''');
CHANGE FIELD(#S_189TXT4) TO('''This can be done with one SELECT_SQL statement.''');
**********;
BEGIN_LOOP;
CLR_LIST NAMED(#S_LSTPSL);
SELECT_SQL FIELDS((#S_189SUM 'SUM(SALARY)') (#S_189CNT 'COUNT(SALARY)') (#S_189AVG 'AVG(SALARY)') (#S_189MAX 'MAX(SALARY)') (#S_189MIN 'MIN(SALARY)')) FROM_FILES((PSLMST));
ADD_ENTRY TO_LIST(#S_LSTPSL);
ENDSELECT;
**********;
DISPLAY FIELDS(#S_189TXT1 #S_189TXT2 #S_189TXT3 #S_189TXT4) DESIGN(*DOWN) IDENTIFY(*NOID) BROWSELIST(#S_LSTPSL);
END_LOOP;