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 an imported 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.
