SELECT_SQL Examples
Using SELECT_SQL With the DISTINCT Option
Using SELECT_SQL With Calculations
Using SELECT_SQL With AND and OR Operators
Using SELECT_SQL With the BETWEEN Operator
Using SELECT_SQL With the DISTINCT Option
This example demonstrates how to use the SELECT_SQL command with the DISTINCT option to eliminate duplicate field values. The use of the standard SELECT_SQL command without any extra options is also demonstrated.
DEF_LIST NAME(#EMPBROWSE) FIELDS(#NDSTEMPNO #DSTEMPNO)
DEFINE FIELD(#HEADING1) TYPE(*CHAR) LENGTH(79) INPUT_ATR(LC)
DEFINE FIELD(#NDSTEMPNO) REFFLD(#EMPNO) COLHDG('Employee number' 'Not Distinct')
DEFINE FIELD(#DSTEMPNO) REFFLD(#EMPNO) COLHDG('Employee Number' 'Distinct')
DEFINE FIELD(#ENTRYNO) TYPE(*DEC) LENGTH(5) DECIMALS(0) DESC('List entry counter')
CHANGE FIELD(#HEADING1) TO('''This function uses SELECT_SQL from PSLSKL.''')
BEGIN_LOOP
EXECUTE SUBROUTINE(NOTDISTINC)
EXECUTE SUBROUTINE(DISTINCT)
DISPLAY FIELDS(#HEADING1) DESIGN(*DOWN) IDENTIFY(*NOID) BROWSELIST(#EMPBROWSE)
END_LOOP
SUBROUTINE NAME(NOTDISTINC)
CLR_LIST NAMED(#EMPBROWSE)
CHANGE FIELD(#DSTEMPNO) TO(*NULL)
SELECT_SQL FIELDS(#EMPNO) FROM_FILES((PSLSKL))
CHANGE FIELD(#NDSTEMPNO) TO(#EMPNO)
ADD_ENTRY TO_LIST(#EMPBROWSE)
ENDSELECT
ENDROUTINE
SUBROUTINE NAME(DISTINCT)
CHANGE FIELD(#ENTRYNO) TO(1)
SELECT_SQL FIELDS(#EMPNO) FROM_FILES((PSLSKL)) DISTINCT(*YES)
GET_ENTRY NUMBER(#ENTRYNO) FROM_LIST(#EMPBROWSE)
CHANGE FIELD(#DSTEMPNO) TO(#EMPNO)
UPD_ENTRY IN_LIST(#EMPBROWSE)
CHANGE FIELD(#ENTRYNO) TO('#ENTRYNO + 1')
ENDSELECT
ENDROUTINE
Using SELECT_SQL With Calculations
This example demonstrates how calculations can be used on date retrieved by the SELECT_SQL command.
DEF_LIST NAME(#EMPBROWSE) FIELDS(#SURNAME #SALARY #STD_AMNT)
DEFINE FIELD(#HEADING1) TYPE(*CHAR) LENGTH(79) INPUT_ATR(LC)
DEFINE FIELD(#HEADING2) TYPE(*CHAR) LENGTH(79) INPUT_ATR(LC)
DEFINE FIELD(#HEADING3) TYPE(*CHAR) LENGTH(79) INPUT_ATR(LC)
OVERRIDE FIELD(#STD_AMNT) COLHDG('Salary + 10%')
CHANGE FIELD(#HEADING1) TO('''This function uses SELECT_SQL from PSLMST.''')
CHANGE FIELD(#HEADING2) TO('''This shows a list of employee surnames and salaries and the salary + 10%.''')
CHANGE FIELD(#HEADING3) TO('''This can be done with one SELECT_SQL statement.''')
BEGIN_LOOP
CLR_LIST NAMED(#EMPBROWSE)
SELECT_SQL FIELDS(#SURNAME #SALARY (#STD_AMNT 'SALARY * 1.10')) FROM_FILES((PSLMST))
ADD_ENTRY TO_LIST(#EMPBROWSE)
ENDSELECT
DISPLAY FIELDS(#HEADING1 #HEADING2 #HEADING3) DESIGN(*DOWN) IDENTIFY(*NOID) BROWSELIST(#EMPBROWSE)
END_LOOP
Using SELECT_SQL With AND and OR Operators
This example demonstrates how the SLECT_SQL command can be used with AND and OR operators to conduct more complex queries.
DEF_LIST NAME(#EMPBROWSE) FIELDS(#EMPNO #ADDRESS2 #SALARY #SURNAME)
DEFINE FIELD(#HEADING1) TYPE(*CHAR) LENGTH(79) INPUT_ATR(LC)
DEFINE FIELD(#HEADING2) TYPE(*CHAR) LENGTH(79) INPUT_ATR(LC)
DEFINE FIELD(#HEADING3) TYPE(*CHAR) LENGTH(79) INPUT_ATR(LC)
CHANGE FIELD(#HEADING1) TO('''This function uses SELECT_SQL from PSLMST.''')
CHANGE FIELD(#HEADING2) TO('''This lists all employees who either have a salary in the range 10000 to 20000,''')
CHANGE FIELD(#HEADING3) TO('''or who live in SEVEN HILLS. This can be done with one SELECT_SQL statement.''')
BEGIN_LOOP
CLR_LIST NAMED(#EMPBROWSE)
SELECT_SQL FIELDS(#EMPNO #SURNAME #ADDRESS2 #SALARY) FROM_FILES((PSLMST)) WHERE('((SALARY > 10000) AND (SALARY < 20000)) OR (ADDRESS2 = ''SEVEN HILLS.'')')
ADD_ENTRY TO_LIST(#EMPBROWSE)
ENDSELECT
DISPLAY FIELDS(#HEADING1 #HEADING2 #HEADING3) DESIGN(*DOWN) IDENTIFY(*NOID) BROWSELIST(#EMPBROWSE)
END_LOOP
Using SELECT_SQL With the BETWEEN Operator
This example demonstrates the use of the SELECT_SQL command with the BETWEEN operator. The BETWEEN operator can be used in the WHERE clause to retrieve data between specified values. It can also be used to retrieve data excluding that between specified values.
DEF_LIST NAME(#EMPBROWSE) FIELDS(#EMPNO #SALARY)
DEFINE FIELD(#HEADING1) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC)
DEFINE FIELD(#HEADING2) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC)
DEFINE FIELD(#HEADING3) TYPE(*CHAR) LENGTH(079) INPUT_ATR(LC)
DEF_COND NAME(*AS400) COND('*CPUTYPE = AS400')
CHANGE FIELD(#HEADING1) TO('''EXAMPLE 1: Select all employees with a salary between 30,000 and 60,000.''')
CHANGE FIELD(#HEADING2) TO(*BLANKS)
CHANGE FIELD(#HEADING3) TO('''This can be done with one SELECT_SQL statement.''')
BEGIN_LOOP
CHANGE FIELD(#HEADING1) TO('''EXAMPLE 1: Select all employees with a salary between 30,000 and 60,000.''')
CLR_LIST NAMED(#EMPBROWSE)
SELECT_SQL FIELDS(#EMPNO #SALARY) FROM_FILES((PSLMST)) WHERE('SALARY BETWEEN 30000 AND 60000')
ADD_ENTRY TO_LIST(#EMPBROWSE)
ENDSELECT
EXECUTE SUBROUTINE(DISP)
CHANGE FIELD(#HEADING1) TO('''EXAMPLE 2: Select all employees with a salary outside range 30,000 to 60,000.''')
CLR_LIST NAMED(#EMPBROWSE)
SELECT_SQL FIELDS(#EMPNO #SALARY) FROM_FILES((PSLMST)) WHERE('SALARY NOT BETWEEN 30000 AND 60000')
ADD_ENTRY TO_LIST(#EMPBROWSE)
ENDSELECT
EXECUTE SUBROUTINE(DISP)
END_LOOP
SUBROUTINE NAME(DISP)
DISPLAY FIELDS(#HEADING1 #HEADING2 #HEADING3) DESIGN(*DOWN) IDENTIFY(*NOID) BROWSELIST(#EMPBROWSE)
ENDROUTINE
For more examples of the SELECT_SQL command please see ‘All About SELECT_SQL’ in The Set Collection.