SELECT_SQL Free Format Examples

LANSA Technical

previous page next page

8.19.2 SELECT_SQL Free Format Examples

Using SELECT_SQL With the DISTINCT Option

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) USING('SELECT "EMPNO" FROM "XDEMOLIB"."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) USING('SELECT DISTINCT "EMPNO" FROM "XDEMOLIB"."PSLSKL"')
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)
           USING('SELECT "SURNAME", "SALARY", "SALARY" * 1.10 FROM "XDEMOLIB"."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)

USING('SELECT "EMPNO", "SURNAME", "ADDRESS2", "SALARY" FROM "XDEMOLIB"."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)
           USING('SELECT "EMPNO", "SALARY", FROM "XDEMOLIB"."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)
           USING('SELECT "EMPNO", "SALARY", FROM "XDEMOLIB"."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.

previous page start next page