7.110.2 SELECT_SQL Free Format 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 to execute a Microsoft SQL Server Stored Procedure

Using SELECT_SQL to execute an IBM i User-Defined Table Function (UDTF)

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

Using SELECT_SQL to execute a Microsoft SQL Server Stored Procedure

The examples below are based on a PC Other file called EmployeeDATA which contains these fields:
#EMPNUMBER(Key), LASTNAME, FIRSTNAME and EMP_AGE. A stored procedure is also used: FindEmployeesOverX

The stored procedure can be created in SQL Server with:

CREATE PROCEDURE FindEmployeesOverX

      -- Add the parameters for the stored procedure here

      @Age Decimal(18) = 35,

 

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

   -- Insert statements for procedure here

      SELECT EMPNUMBER, LASTNAME, FIRSTNAME, EMP_AGE

      from StoredProcedureTest.dbo.EmployeeDATA

      where EMP_AGE <= @Age

END

The following example shows how you can access a stored procedure that does not require any input to get results back as it will take the default of 35 for @Age

DEF_LIST NAME(#EMPLOYEES) FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME #EMP_AGE) TYPE(*WORKING)

CLR_LIST NAMED(#EMPLOYEES)

SELECT_SQL FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME #EMPAGE ) FROM_FILES((EMPLOYEEDATA)) USING(DBO.FINDEMPLOYEESOVERX)

ADD_ENTRY TO_LIST(#EMPLOYEES)

ENDSELECT

Using the same procedure, you are able to use your own search value for building the query and then pass it as string with the USING parameter.

DEF_LIST NAME(#EMPLOYEES) FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME #EMP_AGE) TYPE(*WORKING)

CLR_LIST NAMED(#EMPLOYEES)

DEFINE FIELD(#AGE) TYPE(*SIGNED) LENGTH(3) DECIMALS(0) DEFAULT(25)

DEFINE FIELD(#SQLQUERY) TYPE(*CHAR) LENGTH(25)

#SQLQUERY:= "DBO. FINDEMPLOYEESOVERX@AGE = '" + #AGE + "'"

SELECT_SQL FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME #EMP_AGE) FROM_FILES((EMPLOYEEDATA)) USING(#SQLQUERY)

ADD_ENTRY TO_LIST(#EMPLOYEES)

ENDSELECT

It is important to note if you are using a subset of columns that you only ever pass the column that will be used in the procedures query.

For example, if the SQL query from procedure 'ReturnEmployeeNumbersOnly' was:

      SELECT EmpNumber

      from StoredProcedureDB.dbo.EmployeeDATA

The following Select SQL statement would fail at run time:

SELECT_SQL FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME #EMPAGE ) FROM_FILES((EMPLOYEEDATA)) USING(DBO.RETURNEMPLOYEENUMBERSONLY)

Instead it would have to formatted as:

SELECT_SQL FIELDS(#EMPNUMBER) FROM_FILES((EMPLOYEEDATA)) USING(DBO.RETURNEMPLOYEENUMBERSONLY)

This also applies to column ordering, if the statement from procedure FindEmployeesNamed was:

      SELECT EMPNUMBER, FirstName, LastName

      from StoredProcedureTest.dbo.EmployeeDATA

      where FirstName = @GivenName

The following statement would return firstnames in the last names column and vice-versa:

SELECT_SQL FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME ) FROM_FILES((EMPLOYEEDATA)) USING(DBO.FINDEMPLOYEESNAMED)

Instead it would need to be formatted as:

SELECT_SQL FIELDS(#EMPNUMBER #FIRSTNAME #LASTNAME ) FROM_FILES((EMPLOYEEDATA)) USING(DBO.FINDEMPLOYEESNAMED)

Using SELECT_SQL to execute an IBM i User-Defined Table Function (UDTF)

The examples below are based on the arcust and entauthlist files in a library named modernize. A user-defined table is also used: branchCustomers

The user-defined table and an index can be created in DB2 on the IBM i with:

set schema modernize;

set path modernize;

create index arcustinx1 on arcust (RCWHSE);

label on index arcustinx1 is 'from Index Advisor';

 

create or replace function branchCustomers

  (prmjob char(10), prmuser char(10), prmnbr char(10), prmtype char(10), prmloc char(3))         

  returns table (RCDLCD char(1), RCCST# char(10), RCNAME char(35), RCADR1 char(25), RCADR2 char(25), RCCITY char(23), RCST char(2),

                 RCZIP numeric(5,0), RCXZIP numeric(4,0), RCAREA numeric(3,0), RCPHON numeric(7,0), RCCONT char(20), RCMACT numeric(8,0),

                 RCSTA1 char(25), RCSTA2 char(25), RCSTCT char(23), RCSTST char(2), RCSTZP numeric(5,0), RCSTXZ numeric(4,0))

  LANGUAGE SQL

  DETERMINISTIC

  NOT FENCED

  NO EXTERNAL ACTION

  CARDINALITY 100

  BEGIN

    RETURN

      with EA AS

        (select xxentity from entauthlst

         where xxjob=prmjob and xxuser=prmuser and xxnbr=prmnbr and xxrectype=prmtype

         and (prmloc= ' ' or xxentity=prmloc))

 

        select RCDLCD, RCCST#, RCNAME, RCADR1, RCADR2, RCCITY, RCST,

                 RCZIP, RCXZIP, RCAREA, RCPHON, RCCONT, RCMACT,

                 RCSTA1, RCSTA2, RCSTCT, RCSTST, RCSTZP, RCSTXZ

          from arcust

          where rcwhse in (select xxentity from EA);

    END;

label on function branchCustomers is 'ARCUST search filtered by ENTAUTHLST';

Following are the SELECT_SQL statements for working with the UDTF:

#SQLQRY := 'SELECT RCDLCD, RCCST#, RCNAME, RCADR1, RCADR2, RCCITY, RCST, RCZIP, RCXZIP, RCAREA, RCPHON, RCCONT, RCMACT, RCSTA1, RCSTA2, RCSTCT, RCSTST, RCSTZP, RCSTXZ, RMCR'

 

#SQLQRY += ' from table(branchCustomers(' + #QUOTE + 'TESTJOBNAM' + #QUOTE + ', ' + #QUOTE + #XXUSER + #QUOTE + ', ' + #QUOTE + '123456' + #QUOTE + ', ' + #QUOTE + 'ALPHAWHSES' + #QUOTE + ', ' + #QUOTE + '354' + #QUOTE + ')) as BC'

 

If (#sql_where ^= *BLANKS)

#SQLQRY += ' WHERE ' + #sql_where.TRIM

Endif

 

Select_Sql Fields(#RCDLCD #RCCST# #RCNAME #RCADR1 #RCADR2 #RCCITY #RCST #RCZIP #RCXZIP #RCAREA #RCPHON #RCCONT #RCMACT #RCSTA1 #RCSTA2 #RCSTCT #RCSTST #RCSTZP #RCSTXZ #RMCR) Using(#SQLQRY)

 

Note that the SELECT_SQL FIELDS list of fields must be in the same order as the list of fields in the SELECT statement in the SELECT_SQL USING parameter.