AS400/IBMi DB2とOracleとのSQL違いまとめ

なんだこりゃ。

勉強のためと思ってOracle12Cを一生懸命勉強したのにAS400で使えない命令があるなんて聞いてねーぞ。

ということで、少しだけ調べてみました。

間違いもあるかもしれませんので、ご了承のうえご覧ください。

 

最近、新AS400に買い替えました。昔は使えなかったのに、使えるようになったものがあります。

一応全て動作確認しました。2019年9月 新IBMi Power9 Ver7.3で確認済。

 

事前準備

Oracle白本と同じようにemployeesテーブルを作る。

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のデータ型参照

CREATE TABLE HOGELIB.DUAL (DUMMY VARCHAR(1));

 

１件だけデータを放り込む。

UPDDTA HOGELIB.DUAL
からのOracleと同じようにＸを入力。

 

基礎

テーブルの列名や型を調べる DESC/DESCRIBE 使えない

DESC HOGELIB.EMPLOYEES;

剰余算 % 使えない

%で余りを計算することはできない。これはOracleも同じ。

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 使える

SELECT DISTINCT JOB FROM HOGELIB.EMPLOYEES;

//複数列の組み合わせによる重複行の削除も使える。ただし並びが少しおかしい気がする。
SELECT DISTINCT DEPTNO , JOB FROM HOGELIB.EMPLOYEES;

 

関数編

大文字にする UPPER関数 使える

（ウチのASは小文字は使用してないので、意味ないけど）

SELECT YOMI,UPPER(YOMI) FROM HOGELIB.EMPLOYEES;

小文字にする LOWER関数 使える

SELECT YOMI,LOWER(YOMI) FROM HOGELIB.EMPLOYEES;

関数をWHERE句で使用 使える

SELECT YOMI FROM HOGELIB.EMPLOYEES WHERE LOWER(YOMI) = 'takahashi';

UPPERとLOWERを文字リテラルに使用 使える

SELECT 'small char' , UPPER('small char') FROM HOGELIB.DUAL;

最初の文字を大文字にする INITCAP 使えない

SELECT INITCAP('oracle') FROM HOGELIB.DUAL; //Oracleにはならない

文字の連結 CONCAT 使える

Oracleと同じで引数は２つまで。

SELECT CONCAT('Oracle','Master') FROM HOGELIB.DUAL;

３つ以上の文字連結 ||（パイプ）使える

CONCATは２つの文字連結だけ。３つ以上はパイプを使う。

SELECT 'Oracle' || 'Server' || 'Machine' FROM HOGELIB.DUAL;

文字の切り出し SUBSTR関数 使える

何文字目から何文字文の文字列を戻す。０始まりではなく１始まりです。

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 使える

文字数というかバイト数をシフト文字ごとカウントしてくれるみたい。

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で探し始める位置も指定できるのでこれでいいと思う。

//一番左を１と数えて９文字目、最初の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では使えなかったが使えるようになった。

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関数 使える

第１引数の中に、第２引数の文字があった場合、第３引数の文字に置き換える。

SELECT REPLACE('Oracle Server','Server','Master') FROM HOGELIB.DUAL; //Oracle Master

文字列左右の空白文字を削除する TRIM関数 使える

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) 使える

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) 使える

SELECT TRIM(TRAILING 'r' FROM 'Oracle Server') FROM HOGELIB.DUAL;  //Oracle Serve

先行及び後続文字を置換する TRIM関数(BOTH) 使える

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)とする。

引数は与えられないので桁はデータ型の桁数になるみたい。

SELECT * FROM YEAR||DIGITS(MONTH) FROM HOGELIB.DUAL;

日付を文字に変換する TO_CHARやCHAR

文字や数字はSUBSTRで切れるけど日付は切れない。そういうときに変換するのがこれ。

例えばタイムスタンプは「2020-07-07 15:56:48.192009」といった形。

-- 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 使える

SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES WHERE SAL NOT BETWEEN 200000 AND 300000;

IN演算子もNOT IN演算子 使える

SELECT EMPNO,ENAME,DEPTNO FROM HOGELIB.EMPLOYEES WHERE DEPTNO NOT IN (10,20);

ORDER BYの列別名指定 できる

Oracleと同じで””で指定した場合は、””で指定する。

SELECT EMPNO,ENAME,SAL*12 "Annsal" FROM HOGELIB.EMPLOYEES ORDER BY "Annsal";

ORDER BYの列番号指定 できる

SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY 3;

指定数の行を飛ばして抽出する OFFSET 使える

上から何行飛ばすかをOFFSET ? ROWSで指定する。１を指定したら１行飛ばすので２行目から抽出することになる。

*以前のIBMiでは使えなかったが使えるようになった。

//ちなみに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では使えなかった。

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が後。

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ではもちろん使える。

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が有効でないと怒られる。

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も使えない。

SELECT EMPNO,ENAME,COMM FROM HOGELIB.EMPLOYEES ORDER BY COMM NULLS FIRST;

グルーピングのROLLUP 使える

そもそもROLLUPが使えたためしがない・・。

省略～　GROUP BY A40,A41,ROLLUP(A42,A43)) ORDER BY A40,A41,A42,A43;