SELECT_SQL を使って Microsoft SQL Server のストアド・プロシージャを実行
SELECT_SQL を使って IBM i ユーザー定義テーブル・ファンクション (UDTF) を実行
以下の例は、SELECT_SQLコマンドでDISTINCTオプションを指定して、重複したフィールド値を排除する方法を示しています。また、追加オプションを指定しない標準のSELECT_SQLコマンドの使用方法も示します。
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
以下の例は、SELECT_SQLコマンドで取得した日付に対して計算を実行する方法を示しています。
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
以下の例は、SLECT_SQLコマンドでANDおよびOR演算子を使用して、さらに複雑なクエリーを実行する方法を示しています。
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
以下の例は、SELECT_SQLコマンドでBETWEEN演算子を使用する方法を示しています。BETWEEN演算子は、指定された値範囲のデータを取得するためにWHERE句内で使用できます。また、指定された値範囲を除外したデータを取得することもできます。
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
SELECT_SQL を使って Microsoft SQL Server のストアド・プロシージャを実行
以下の例では、次のフィールドを含むインポート・ファイル EmployeeDATA がベースになっています。
#EMPNUMBER(Key), LASTNAME, FIRSTNAME and EMP_AGE.ストアド・プロシージャは、FindEmployeesOverX でも使用されています。
ストア・プロシージャは、CREATE PROCEDURE FindEmployeesOverX で SQL Server 内に作成されます。
CREATE PROCEDURE FindEmployeesOverX
-- ストアド・プロシージャのパラメータをここに追加
@Age Decimal(18) = 35,
AS
BEGIN
-- SELECT ステートメントの干渉による余分な結果を避けるため
-- SET NOCOUNT ON を追加
SET NOCOUNT ON;
-- プロシージャのステートメントをここに挿入
SELECT EMPNUMBER, LASTNAME, FIRSTNAME, EMP_AGE
from StoredProcedureTest.dbo.EmployeeDATA
where EMP_AGE <= @Age
END
次の例は、@Age でデフォルト値 35 を使うため、結果を得るために入力が不必要なストア・プロシージャへのアクセス方法が示されています。
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 で文字列として引き渡すことができます。
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
列のサブセットを使用している場合、プロシージャのクエリで使用される列のみを引き渡すように注意することが大切です。
例えば、プロシージャ 'ReturnEmployeeNumbersOnly' からの SQL クエリーが以下の場合:
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)
代わりに、次のような形式にする必要があります。
SELECT_SQL FIELDS(#EMPNUMBER) FROM_FILES((EMPLOYEEDATA)) USING(DBO.RETURNEMPLOYEENUMBERSONLY)
列順序にも同様のことが当てはまります。プロシージャ FindEmployeesNamed からのステートメントが以下の場合:
SELECT EMPNUMBER, FirstName, LastName
from StoredProcedureTest.dbo.EmployeeDATA
where FirstName = @GivenName
次のステートメントでは、苗字の列に名前が、苗字の列に名前が返されてしまいます。
SELECT_SQL FIELDS(#EMPNUMBER #LASTNAME #FIRSTNAME ) FROM_FILES((EMPLOYEEDATA)) USING(DBO.FINDEMPLOYEESNAMED)
代わりに、次のような形式にする必要があります。
SELECT_SQL FIELDS(#EMPNUMBER #FIRSTNAME #LASTNAME ) FROM_FILES((EMPLOYEEDATA)) USING(DBO.FINDEMPLOYEESNAMED)
SELECT_SQL を使って IBM i ユーザー定義テーブル・ファンクション (UDTF) を実行
次の例はライブラリ modernize 内のファイル arcust と entauthlist がベースとなっています。ユーザー定義テーブルは branchCustomers でも使用されています。
ユーザー定義テーブルとインデックスは、次で IBM i の DB2 内に作成できます。
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';
以下は UDTF で作業する SELECT_SQL ステートメントです。
#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)
フィールドの SELECT_SQL FIELDS リストは、SELECT_SQL USING パラメータの SELECT ステートメントのフィールド・リストと同じ順序でなければいけないことに注意してください。
