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

IT関連

なんだこりゃ。

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

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

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

 

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

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

 

  1. 事前準備
  2. 基礎
    1. テーブルの列名や型を調べる DESC/DESCRIBE 使えない
    2. 剰余算 % 使えない
    3. 四則演算の優先順位 ()で指定できる
    4. 別名 AS 使える
    5. NULLの表示?
    6. 重複行の削除 DISTINCT 使える
  3. 関数編
    1. 大文字にする UPPER関数 使える
    2. 小文字にする LOWER関数 使える
    3. 関数をWHERE句で使用 使える
    4. UPPERとLOWERを文字リテラルに使用 使える
    5. 最初の文字を大文字にする INITCAP 使えない
    6. 文字の連結 CONCAT 使える
    7. 3つ以上の文字連結 ||(パイプ)使える
    8. 文字の切り出し SUBSTR関数 使える
    9. 文字数を数える LENGTH 使える
    10. 指定文字の位置を戻す INSTR関数 使える
    11. 合計文字数を指定して文字で桁埋め LPAD、RPAD関数 使える
    12. 文字の置き換え/置換 REPLACE関数 使える
    13. 文字列左右の空白文字を削除する TRIM関数 使える
    14. 先行する文字を置換する TRIM関数(LEADING) 使える
    15. 後続する文字を置換する TRIM関数(TRAILING) 使える
    16. 先行及び後続文字を置換する TRIM関数(BOTH) 使える
    17. 月や日の先頭の0を表示する TO_CHARは使えないが DIGITS関数 使える
    18. 日付を文字に変換する TO_CHARやCHAR
  4. 制限及びソート編
    1. Where句で範囲で指定する BETWEEN も NOT BETWEEN 使える
    2. IN演算子もNOT IN演算子 使える
    3. ORDER BYの列別名指定 できる
    4. ORDER BYの列番号指定 できる
    5. 指定数の行を飛ばして抽出する OFFSET 使える
    6. 指定数の行数を抽出する FETCH FIRST ROWS ONLY 使える
    7. OFFSETとFETCHの組み合わせ 使える
    8. ソート行の重複を含める FETCH WITH TIES 使えない
    9. FETCH FIRSTを%(割合)で指定 PERCENT ROWS ONLY 使えない
    10. NULL行を最初に持ってくるか最後に持ってくるか NULLS FIRST | NULLS LAST 使えない
    11. グルーピングのROLLUP 使える

事前準備

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));

 

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

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

 

基礎

テーブルの列名や型を調べる 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は自動で0が補完されるようだ。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と同じで引数は2つまで。

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

3つ以上の文字連結 ||(パイプ)使える

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

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

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

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

SELECT SUBSTR('Oracle Server',2,3) FROM DUAL; //rac

//3つ目の引数を省略すると以降の文字列を全て返す
SELECT SUBSTR('Oracle Server',2) FROM DUAL; //racle Server

//1始まりなので、0にするとさらに1つ前から取得を試みる意味不明仕様
SELECT SUBSTR('Oracle Server',0,1) FROM DUAL; //ブランク

//第1引数を負にすれば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で探し始める位置も指定できるのでこれでいいと思う。

//一番左を1と数えて9文字目、最初のerがヒットする
SELECT INSTR('Oracle Server','er') FROM HOGELIB.DUAL; //9

//第三引数は一番左の何文字目から探すかを指定する

//10文字目から探すので
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関数 使える

第2引数を文字数、第3引数に桁埋め文字を指定すると、第2引数の合計桁数になるように左か右に第3引数の文字を埋め込む。

*以前の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関数 使える

第1引数の中に、第2引数の文字があった場合、第3引数の文字に置き換える。

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←半角スペース2つの場合も除去

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(全角スペース除去)

月や日の先頭の0を表示する 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で指定する。1を指定したら1行飛ばすので2行目から抽出することになる。

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

//ちなみにROWSはROWでもOK
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES OFFSET 1 ROWS; //2行目から出力
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES OFFSET 5 ROWS; //6行目から出力

指定数の行数を抽出する FETCH FIRST ROWS ONLY 使える

抽出する行数を指定できる。3なら3行だけ。1なら1行だけ。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; //1行だけ抽出
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH FIRST 3 ROWS ONLY; //3行だけ抽出
SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH FIRST 5 ROWS ONLY; //5行だけ抽出

SELECT EMPNO,ENAME,SAL FROM HOGELIB.EMPLOYEES ORDER BY SAL DESC FETCH NEXT 3 ROWS ONLY; //3行だけ抽出

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をすると、指定した3行を抽出するお約束だが、ORDER BYで並び替えた列の値が重複した場合は、どちらをもってきたらいいか解らない。

解りやすくいうと1位・2位・3位・3位・4位の場合。WITH TIESを使えば3行を抽出するお約束だったとしても3位は2つあるので計4行で抽出してくれる。

でもこの機能は使えないみたい。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;

 

コメント

タイトルとURLをコピーしました