AS400 IT備忘録 SQL リファレンス

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

投稿日:2019年10月1日 更新日:

なんだこりゃ。

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

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

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

 

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

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

 

事前準備

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

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

文字数を数える LENGTH 使える

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

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

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

合計文字数を指定して文字で桁埋め 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)とする。

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

 

 

制限及びソート編

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が使えたためしがない・・。

 

スポンサーリンク




スポンサーリンク




-AS400, IT備忘録, SQL, リファレンス

執筆者:


comment

Your email address will not be published. Required fields are marked *

関連記事

MacでプリンタドライバーCanonLBP8610を設定する

自分用の備忘録なので適当です。Windowsしか設定したことがなくハマったので残しておきます。   まずはメーカーのHPからドライバーをダウンロードする。   Windowsはイン …

XAMPPのPHPでメール送信する

どこを調べてもPHPでメールを送るのは簡単だ。と、ドヤ顔でこんなソースを載せている。それは解ったんだけど、mb_send_mailが使えないんだよ。とブツブツ言いながら調べてphp.iniを直したりな …

AS400/IBMi エラーまとめ(随時更新)

エラー関係のまとめです。書きかけ。   SQL0802 データ変換またはデータ・マッピング・エラー Vendor Code: -802 General error: -802 (SQLExe …

AS400/IBMi PHPで配列の文字コードを一気に変換する

AS400からODBCでデータを取得して表示したら、私の環境ではSJISのためそのまま画面に表示すると文字化けする。   ソースコードなどはUTF8で統一しているため、SJIS統一はしない方 …

AS400/IBMi ACSの印刷設定

ACSの導入テスト時でレーザープリンターの設定でハマったので備忘録。 テストはMacで行いましたが、Windowsも同じだと思います。   現状 使用プリンターはキャノンLBP8610という …

Adsense広告


文字で検索

人気記事

なぜだか人気記事

読んでほしい順に