SQL 自分の備忘録 AS400とOracle違い

なんだこりゃ。Oracle12cSQLを一生懸命勉強したのに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));

 

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

何文字目から何文字文の文字列を戻す。

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では使えなかったが使えるようになった。

//一番左を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

合計文字数を指定して文字で桁埋め 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;

 

 

制限及びソート編

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;

 

author

© 2021 なんで勉強するの