なんだこりゃ。
勉強のためと思ってOracle12Cを一生懸命勉強したのにAS400で使えない命令があるなんて聞いてねーぞ。
ということで、少しだけ調べてみました。
間違いもあるかもしれませんので、ご了承のうえご覧ください。
最近、新AS400に買い替えました。昔は使えなかったのに、使えるようになったものがあります。
一応全て動作確認しました。2019年9月 新IBMi Power9 Ver7.3で確認済。
- 事前準備
- 基礎
- 関数編
- 大文字にする UPPER関数 使える
- 小文字にする LOWER関数 使える
- 関数をWHERE句で使用 使える
- UPPERとLOWERを文字リテラルに使用 使える
- 最初の文字を大文字にする INITCAP 使えない
- 文字の連結 CONCAT 使える
- ３つ以上の文字連結 ||（パイプ）使える
- 文字の切り出し SUBSTR関数 使える
- 文字数を数える LENGTH 使える
- 指定文字の位置を戻す INSTR関数 使える
- 合計文字数を指定して文字で桁埋め LPAD、RPAD関数 使える
- 文字の置き換え/置換 REPLACE関数 使える
- 文字列左右の空白文字を削除する TRIM関数 使える
- 先行する文字を置換する TRIM関数(LEADING) 使える
- 後続する文字を置換する TRIM関数(TRAILING) 使える
- 先行及び後続文字を置換する TRIM関数(BOTH) 使える
- 月や日の先頭の０を表示する TO_CHARは使えないが DIGITS関数 使える
- 日付を文字に変換する TO_CHARやCHAR
- 制限及びソート編
- Where句で範囲で指定する BETWEEN も NOT BETWEEN 使える
- IN演算子もNOT IN演算子 使える
- ORDER BYの列別名指定 できる
- ORDER BYの列番号指定 できる
- 指定数の行を飛ばして抽出する OFFSET 使える
- 指定数の行数を抽出する FETCH FIRST ROWS ONLY 使える
- OFFSETとFETCHの組み合わせ 使える
- ソート行の重複を含める FETCH WITH TIES 使えない
- FETCH FIRSTを％（割合）で指定 PERCENT ROWS ONLY 使えない
- NULL行を最初に持ってくるか最後に持ってくるか NULLS FIRST | NULLS LAST 使えない
- グルーピングのROLLUP 使える
事前準備
Oracle白本と同じようにemployeesテーブルを作る。
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE HOGELIB.EMPLOYEES ( EMPNO NUMERIC(4) NOT NULL, ENAME VARCHAR(10), YOMI VARCHAR(20), JOB VARCHAR(8), MGR NUMERIC(4), HIREDATE DATE, SAL NUMERIC(7), COMM NUMERIC(7), DEPTNO NUMERIC(2) ); |
1001佐藤社長から1014佐々木までDFUで放り込んでいく。
DUALが使えないので、ダミーのテーブル作り。ORACLEはVARCHAR2(1)だが、使えないので下記にした。DB2のデータ型参照
|
1 |
CREATE TABLE HOGELIB.DUAL (DUMMY VARCHAR(1)); |
１件だけデータを放り込む。
UPDDTA HOGELIB.DUAL
からのOracleと同じようにＸを入力。
基礎
テーブルの列名や型を調べる DESC/DESCRIBE 使えない
|
1 |
DESC HOGELIB.EMPLOYEES; |
剰余算 % 使えない
%で余りを計算することはできない。これはOracleも同じ。
|
1 2 3 4 |
SELECT 10 + 20 FROM HOGELIB.DUAL; SELECT 20 - 10 FROM HOGELIB.DUAL; SELECT 20 - 10 FROM HOGELIB.DUAL; SELECT 20 - 10 FROM HOGELIB.DUAL; |
四則演算の優先順位 ()で指定できる
SELECT 10 * 10 + 2 FROM HOGELIB.DUAL; //102
SELECT 10 * (10 + 2) FROM HOGELIB.DUAL; //120
別名 AS 使える
別名の指定はASや””がなくても指定できる。””がなければ全て大文字になる。Oracleと同じ。
SELECT 10 AS kazu , 20 “kazu” FROM HOGELIB.DUAL;
NULLの表示？
AS400はあまりNULLを聞かないというか嫌うせいか、NULLは自動で０が補完されるようだ。SAL+COMMで、COMMがNULLでも0が入っている。
SELECT SAL , COMM , SAL + COMM FROM HOGELIB.EMPLOYEES;
重複行の削除 DISTINCT 使える
|
1 2 3 4 |
SELECT DISTINCT JOB FROM HOGELIB.EMPLOYEES; //複数列の組み合わせによる重複行の削除も使える。ただし並びが少しおかしい気がする。 SELECT DISTINCT DEPTNO , JOB FROM HOGELIB.EMPLOYEES; |
関数編
大文字にする UPPER関数 使える
（ウチのASは小文字は使用してないので、意味ないけど）
|
1 |
SELECT YOMI,UPPER(YOMI) FROM HOGELIB.EMPLOYEES; |
小文字にする LOWER関数 使える
|
1 |
SELECT YOMI,LOWER(YOMI) FROM HOGELIB.EMPLOYEES; |
関数をWHERE句で使用 使える
|
1 |
SELECT YOMI FROM HOGELIB.EMPLOYEES WHERE LOWER(YOMI) = 'takahashi'; |
UPPERとLOWERを文字リテラルに使用 使える
|
1 |
SELECT 'small char' , UPPER('small char') FROM HOGELIB.DUAL; |
最初の文字を大文字にする INITCAP 使えない
|
1 |
SELECT INITCAP('oracle') FROM HOGELIB.DUAL; //Oracleにはならない |
文字の連結 CONCAT 使える
Oracleと同じで引数は２つまで。
|
1 |
SELECT CONCAT('Oracle','Master') FROM HOGELIB.DUAL; |
３つ以上の文字連結 ||（パイプ）使える
CONCATは２つの文字連結だけ。３つ以上はパイプを使う。
|
1 |
SELECT 'Oracle' || 'Server' || 'Machine' FROM HOGELIB.DUAL; |
文字の切り出し SUBSTR関数 使える
何文字目から何文字文の文字列を戻す。０始まりではなく１始まりです。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT SUBSTR('Oracle Server',2,3) FROM DUAL; //rac //３つ目の引数を省略すると以降の文字列を全て返す SELECT SUBSTR('Oracle Server',2) FROM DUAL; //racle Server //１始まりなので、０にするとさらに１つ前から取得を試みる意味不明仕様 SELECT SUBSTR('Oracle Server',0,1) FROM DUAL; //ブランク //第１引数を負にすればOracleなら文字列の後ろから取得するが、IBMiは先頭よりさらに前から取得を試みる SELECT SUBSTR('Oracle Server',-1,5) FROM DUAL; //Ora |
文字数を数える LENGTH 使える
文字数というかバイト数をシフト文字ごとカウントしてくれるみたい。
|
1 2 3 |
SELECT LENGTH('Oracle Server') FROM HOGELIB.DUAL; //13 SELECT LENGTH('あOracle Server') FROM HOGELIB.DUAL; //17 SELECT LENGTH('あOracle Serverあ') FROM HOGELIB.DUAL; //21 |
指定文字の位置を戻す INSTR関数 使える
*以前のIBMiでは使えなかったが使えるようになった。
他にもIBMi独自っぽいPOSSTRや、LOCATEやPOSITIONも同じようなことができるが、INSTRで探し始める位置も指定できるのでこれでいいと思う。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
//一番左を１と数えて９文字目、最初のerがヒットする SELECT INSTR('Oracle Server','er') FROM HOGELIB.DUAL; //9 //第三引数は一番左の何文字目から探すかを指定する //１０文字目から探すので SELECT INSTR('Oracle Server','er',10) FROM HOGELIB.DUAL; //12 SELECT INSTR('Oracle Server','ra',2) FROM HOGELIB.DUAL; //2 SELECT INSTR('Oracle Server','ra',3) FROM HOGELIB.DUAL; //0 //Locateを使った例（引数が逆） SELECT LOCATE('er','Oracle Server',10) FROM HOGELIB.DUAL; |
合計文字数を指定して文字で桁埋め LPAD、RPAD関数 使える
第２引数を文字数、第３引数に桁埋め文字を指定すると、第２引数の合計桁数になるように左か右に第３引数の文字を埋め込む。
*以前のIBMiでは使えなかったが使えるようになった。
|
1 2 3 4 5 |
SELECT RPAD('Oracle Server',10,'*') FROM HOGELIB.DUAL; //Oracle Ser SELECT RPAD('Oracle Server',15,'*') FROM HOGELIB.DUAL; //Oracle Sever*** SELECT LPAD('Oracle Server',10,'*') FROM HOGELIB.DUAL; //Oracle Ser SELECT LPAD('Oracle Server',15,'*') FROM HOGELIB.DUAL; //**Oracle Sever |
文字の置き換え/置換 REPLACE関数 使える
第１引数の中に、第２引数の文字があった場合、第３引数の文字に置き換える。
|
1 |
SELECT REPLACE('Oracle Server','Server','Master') FROM HOGELIB.DUAL; //Oracle Master |
文字列左右の空白文字を削除する TRIM関数 使える
|
1 2 3 4 5 6 |
SELECT 'Oracle' FROM HOGELIB.DUAL; //Oracle SELECT ' Oracle ' FROM HOGELIB.DUAL; //_Oracle_ SELECT TRIM(' Oracle ') FROM HOGELIB.DUAL; //Oracle SELECT TRIM(' Oracle ') FROM HOGELIB.DUAL; //Oracle←半角スペース２つの場合も除去 SELECT TRIM(' Oracle ') FROM HOGELIB.DUAL; //＿Oracle＿ ←ただし全角スペースは反応なし |
先行する文字を置換する TRIM関数(LEADING) 使える
|
1 2 |
SELECT TRIM(LEADING 'O' FROM 'Oracle Server') FROM HOGELIB.DUAL; //racle Server SELECT TRIM(LEADING 'r' FROM 'Oracle Server') FROM HOGELIB.DUAL; //Oracle Server(変化なし） |
後続する文字を置換する TRIM関数(TRAILING) 使える
|
1 |
SELECT TRIM(TRAILING 'r' FROM 'Oracle Server') FROM HOGELIB.DUAL; //Oracle Serve |
先行及び後続文字を置換する TRIM関数(BOTH) 使える
|
1 |
SELECT TRIM(BOTH ' ' FROM ' Oracle Master ') FROM HOGELIB.DUAL; //Oracle Master（全角スペース除去） |
月や日の先頭の０を表示する TO_CHARは使えないが DIGITS関数 使える
月日をDATE型ではなく数値や文字型で定義しているとき、和暦(YEAR)と月(MONTH)をつなげて2801などとしたいとき、281と表示されてしまう。
OracleならTO_CHAR(MONTH,’00’)でいいが、AS400の場合は、DIGITS(MONTH)とする。
引数は与えられないので桁はデータ型の桁数になるみたい。
|
1 |
SELECT * FROM YEAR||DIGITS(MONTH) FROM HOGELIB.DUAL; |
日付を文字に変換する TO_CHARやCHAR
文字や数字はSUBSTRで切れるけど日付は切れない。そういうときに変換するのがこれ。
例えばタイムスタンプは「2020-07-07 15:56:48.192009」といった形。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- 2020-07-07 15:56:48.192009（タイムスタンプ型） SELECT CURRENT TIMESTAMP FROM HOGELIB.DUAL; -- 2020-07-07 15:56:48.192009（文字列型） SELECT CHAR(CURRENT TIMESTAMP) FROM HOGELIB.DUAL; -- 2020-07-07 15:56:48.192009（文字列型）CHARと同じ SELECT TO_CHAR(CURRENT TIMESTAMP) FROM HOGELIB.DUAL; -- 2020-07-08（文字列型） SELECT TO_CHAR(CURRENT TIMESTAMP,'YYYY-MM-DD') FROM HOGELIB.DUAL; -- 07/08（文字列型） SELECT TO_CHAR(CURRENT TIMESTAMP,'MM/DD') FROM HOGELIB.DUAL; -- これはエラーになる SELECT CHAR(CURRENT TIMESTAMP,'YYYY-MM-DD') FROM HOGELIB.DUAL; -- 20200708（文字列型） SELECT REPLACE(TO_CHAR(CURRENT TIMESTAMP,'YYYY-MM-DD'),'-','') FROM HOGELIB.DUAL; -- 20200708（文字列型）これでもいい SELECT REPLACE(SUBSTR(CHAR(STAMP),0,11),'-','') FROM HOGELIB.DUAL; |
制限及びソート編
Where句で範囲で指定する BETWEEN も NOT BETWEEN 使える
|
1 |
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES WHERE SAL NOT BETWEEN 200000 AND 300000; |
IN演算子もNOT IN演算子 使える
|
1 |
SELECT EMPNO,ENAME,DEPTNO FROM HOGELIB.EMPLOYEES WHERE DEPTNO NOT IN (10,20); |
ORDER BYの列別名指定 できる
Oracleと同じで””で指定した場合は、””で指定する。
|
1 |
SELECT EMPNO,ENAME,SAL*12 "Annsal" FROM HOGELIB.EMPLOYEES ORDER BY "Annsal"; |
ORDER BYの列番号指定 できる
|
1 |
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY 3; |
指定数の行を飛ばして抽出する OFFSET 使える
上から何行飛ばすかをOFFSET ? ROWSで指定する。１を指定したら１行飛ばすので２行目から抽出することになる。
*以前のIBMiでは使えなかったが使えるようになった。
|
1 2 3 |
//ちなみにROWSはROWでもOK SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES OFFSET 1 ROWS; //２行目から出力 SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES OFFSET 5 ROWS; //６行目から出力 |
指定数の行数を抽出する FETCH FIRST ROWS ONLY 使える
抽出する行数を指定できる。３なら３行だけ。１なら１行だけ。ONLYの指定は必要、ONLYがないと動かない。
尚、FETCH NEXT ROWS ONLYもまったく同じ動きらしいが使える。FETCH NEXTは以前のIBMiでは使えなかった。
|
1 2 3 4 5 |
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH FIRST 1 ROWS ONLY; //１行だけ抽出 SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH FIRST 3 ROWS ONLY; //３行だけ抽出 SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH FIRST 5 ROWS ONLY; //５行だけ抽出 SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH NEXT 3 ROWS ONLY; //３行だけ抽出 |
OFFSETとFETCHの組み合わせ 使える
何行分飛ばして、何行だけ抽出する。これができないとページネーションの処理が難しいはず。
OFFSETが先で、FETCHが後。
|
1 |
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES OFFSET 5 ROWS FETCH FIRST 3 ROWS ONLY; |
ソート行の重複を含める FETCH WITH TIES 使えない
FETCH FIRST 3 ROWS ONLYをすると、指定した３行を抽出するお約束だが、ORDER BYで並び替えた列の値が重複した場合は、どちらをもってきたらいいか解らない。
解りやすくいうと１位・２位・３位・３位・４位の場合。WITH TIESを使えば３行を抽出するお約束だったとしても３位は２つあるので計４行で抽出してくれる。
でもこの機能は使えないみたい。Oracleではもちろん使える。
|
1 |
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH FIRST 3 ROWS WITH TIES; |
尚、ROWSの後の有効なトークンはONLYらしい。なのでFETCH FIRST 3 ROWS ONLY WITH TIESとしてもうまくいかない。
WITHは有効なトークンらしく、その後の候補もエラーメッセージを見ると複数あるようだが、どれを選んでもそういうWITH TIESと同じ動作にはならなかった。
FETCH FIRSTを％（割合）で指定 PERCENT ROWS ONLY 使えない
あまり使い道ないけど、50%だけ抽出するとか。Oracleでは下記構文で使えるが、IBMiでは使えない。PERCENTが有効でないと怒られる。
|
1 |
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH FIRST 50 PERCENT ROWS ONLY; |
NULL行を最初に持ってくるか最後に持ってくるか NULLS FIRST | NULLS LAST 使えない
NULLってそもそもAS400は許さない系なので、あっても使うことないかも。いづれにせよNULLS FIRSTもNULLS LASTも使えない。
|
1 |
SELECT EMPNO,ENAME,COMM FROM HOGELIB.EMPLOYEES ORDER BY COMM NULLS FIRST; |
グルーピングのROLLUP 使える
そもそもROLLUPが使えたためしがない・・。
|
1 |
省略～ GROUP BY A40,A41,ROLLUP(A42,A43)) ORDER BY A40,A41,A42,A43; |
コメント