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テーブルを作る。

 

1001佐藤社長から1014佐々木までDFUで放り込んでいく。

 

DUALが使えないので、ダミーのテーブル作り。ORACLEはVARCHAR2(1)だが、使えないので下記にした。DB2のデータ型参照

 

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

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

 

基礎

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

剰余算 % 使えない

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

四則演算の優先順位 ()で指定できる

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

 

関数編

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

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

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

関数をWHERE句で使用 使える

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

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

文字の連結 CONCAT 使える

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

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

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

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

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

文字数を数える LENGTH 使える

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

指定文字の位置を戻す INSTR関数 使える

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

他にもIBMi独自っぽいPOSSTRや、LOCATEやPOSITIONも同じようなことができるが、INSTRで探し始める位置も指定できるのでこれでいいと思う。

合計文字数を指定して文字で桁埋め LPAD、RPAD関数 使える

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

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

文字の置き換え/置換 REPLACE関数 使える

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

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

先行する文字を置換する TRIM関数(LEADING) 使える

後続する文字を置換する TRIM関数(TRAILING) 使える

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

月や日の先頭の0を表示する TO_CHARは使えないが DIGITS関数 使える

月日をDATE型ではなく数値や文字型で定義しているとき、和暦(YEAR)と月(MONTH)をつなげて2801などとしたいとき、281と表示されてしまう。

OracleならTO_CHAR(MONTH,’00’)でいいが、AS400の場合は、DIGITS(MONTH)とする。

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

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

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

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

 

 

制限及びソート編

Where句で範囲で指定する BETWEEN も NOT BETWEEN 使える

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

ORDER BYの列別名指定 できる

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

ORDER BYの列番号指定 できる

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

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

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

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

抽出する行数を指定できる。3なら3行だけ。1なら1行だけ。ONLYの指定は必要、ONLYがないと動かない。

尚、FETCH NEXT ROWS ONLYもまったく同じ動きらしいが使える。FETCH NEXTは以前のIBMiでは使えなかった。

OFFSETとFETCHの組み合わせ 使える

何行分飛ばして、何行だけ抽出する。これができないとページネーションの処理が難しいはず。

OFFSETが先で、FETCHが後。

ソート行の重複を含める FETCH WITH TIES 使えない

FETCH FIRST 3 ROWS ONLYをすると、指定した3行を抽出するお約束だが、ORDER BYで並び替えた列の値が重複した場合は、どちらをもってきたらいいか解らない。

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

でもこの機能は使えないみたい。Oracleではもちろん使える。

尚、ROWSの後の有効なトークンはONLYらしい。なのでFETCH FIRST 3 ROWS ONLY WITH TIESとしてもうまくいかない。

WITHは有効なトークンらしく、その後の候補もエラーメッセージを見ると複数あるようだが、どれを選んでもそういうWITH TIESと同じ動作にはならなかった。

FETCH FIRSTを%(割合)で指定 PERCENT ROWS ONLY 使えない

あまり使い道ないけど、50%だけ抽出するとか。Oracleでは下記構文で使えるが、IBMiでは使えない。PERCENTが有効でないと怒られる。

 

NULL行を最初に持ってくるか最後に持ってくるか NULLS FIRST | NULLS LAST 使えない

NULLってそもそもAS400は許さない系なので、あっても使うことないかも。いづれにせよNULLS FIRSTもNULLS LASTも使えない。

グルーピングのROLLUP 使える

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

 

コメント

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