Function SET189F
********** COMMENT(=======================================================);
********** COMMENT(Process ........: SET_189);
********** COMMENT(Function .......: SET189F);
********** COMMENT(Created on .....: 22/09/00 at 14:43:32);
********** COMMENT(Description ....: SELECT_SQL - joining a file to itself);
********** COMMENT(Version.........: 1);
********** COMMENT();
********** COMMENT(Full Description: The purpose of this function is to);
********** COMMENT(demonstrate the use of join files in SELECT_SQL stateme);
********** COMMENT(nts);
********** COMMENT();
********** COMMENT(Note: To use variables in SELECT_SQL statements see);
********** COMMENT(example SET_010.);
********** COMMENT();
********** 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();
********** COMMENT(Minimum LANSA release: 8.0);
********** COMMENT();
********** COMMENT(=======================================================);
********** COMMENT(Function control options);
FUNCTION OPTIONS(*DIRECT);
********** COMMENT();
OPEN USE_OPTION(OND);
********** COMMENT(Group and field definitions);
********** COMMENT();
DEF_LIST NAME(#S_LSTPSL) FIELDS((#EMPNO)(#SURNAME)(#SALARY));
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);
********** COMMENT();
CHANGE FIELD(#S_189TXT1) TO('''This function uses SELECT_SQL from PSLMST joined to itself.''');
CHANGE FIELD(#S_189TXT2) TO('''This lists all employees who have a higher salary than employee number A1234.''');
CHANGE FIELD(#S_189TXT3) TO('''This is an example of Auto-Join. This can be done with one SELECT_SQL statement.''');
********** COMMENT();
BEGIN_LOOP;
CLR_LIST NAMED(#S_LSTPSL);
SELECT_SQL FIELDS((#EMPNO 'P2.EMPNO') (#SURNAME 'P2.SURNAME') (#SALARY 'P2.SALARY')) FROM_FILES((PSLMST P1)(PSLMST P2)) WHERE('P1.EMPNO=''A1234'' AND P2.SALARY > P1.SALARY');
ADD_ENTRY TO_LIST(#S_LSTPSL);
ENDSELECT;
********** COMMENT();
DISPLAY FIELDS((#S_189TXT1)(#S_189TXT2)(#S_189TXT3)) DESIGN(*DOWN) IDENTIFY(*NOID) BROWSELIST(#S_LSTPSL);
END_LOOP;
********** COMMENT(=======================================================);
********** COMMENT(Process ........: SET_189);
********** COMMENT(Function .......: SET189F);
********** COMMENT(Created on .....: 22/09/00 at 14:43:32);
********** COMMENT(Description ....: SELECT_SQL - joining a file to itself);
********** COMMENT(Version.........: 1);
********** COMMENT();
********** COMMENT(Full Description: The purpose of this function is to);
********** COMMENT(demonstrate the use of join files in SELECT_SQL stateme);
********** COMMENT(nts);
********** COMMENT();
********** COMMENT(Note: To use variables in SELECT_SQL statements see);
********** COMMENT(example SET_010.);
********** COMMENT();
********** 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();
********** COMMENT(Minimum LANSA release: 8.0);
********** COMMENT();
********** COMMENT(=======================================================);
********** COMMENT(Function control options);
FUNCTION OPTIONS(*DIRECT);
********** COMMENT();
OPEN USE_OPTION(OND);
********** COMMENT(Group and field definitions);
********** COMMENT();
DEF_LIST NAME(#S_LSTPSL) FIELDS((#EMPNO)(#SURNAME)(#SALARY));
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);
********** COMMENT();
CHANGE FIELD(#S_189TXT1) TO('''This function uses SELECT_SQL from PSLMST joined to itself.''');
CHANGE FIELD(#S_189TXT2) TO('''This lists all employees who have a higher salary than employee number A1234.''');
CHANGE FIELD(#S_189TXT3) TO('''This is an example of Auto-Join. This can be done with one SELECT_SQL statement.''');
********** COMMENT();
BEGIN_LOOP;
CLR_LIST NAMED(#S_LSTPSL);
SELECT_SQL FIELDS((#EMPNO 'P2.EMPNO') (#SURNAME 'P2.SURNAME') (#SALARY 'P2.SALARY')) FROM_FILES((PSLMST P1)(PSLMST P2)) WHERE('P1.EMPNO=''A1234'' AND P2.SALARY > P1.SALARY');
ADD_ENTRY TO_LIST(#S_LSTPSL);
ENDSELECT;
********** COMMENT();
DISPLAY FIELDS((#S_189TXT1)(#S_189TXT2)(#S_189TXT3)) DESIGN(*DOWN) IDENTIFY(*NOID) BROWSELIST(#S_LSTPSL);
END_LOOP;