スポンサードリンク

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

なんだこりゃ。Oracle12cSQLを一生懸命勉強したのにAS400で使えない命令があるなんて聞いてねーぞ。

 

ということで、少しだけ調べてみたけど、人様に見せるように作っていない。あくまで自分用だけどこれで完成では全くないが、いろんな勉強に手を広げ過ぎてもう次の更新がいつになるか解らない。

 

準備運動

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佐々木までSTRDFUで放り込んでいく。

 

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

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

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

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

・DESC , DESCRIBEは使えなかった。

DESC HOGELIB.EMPLOYEES;

 

実践

四則演算(+ , – , x , / )は、使えるが、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;

 

四則演算の優先順位は下記のようだが、()で指定できる。
1 * 及び /
2 + 及び -

SELECT 10 * 10 + 2 FROM HOGELIB.DUAL;    //102
SELECT 10 * (10 + 2) FROM HOGELIB.DUAL;   //120

 

別名の指定はASや””がなくても指定できる。””がなくては大文字になる。Oracleと同じ。

SELECT 10 AS KAZU , 20 KAZU FROM HOGELIB.DUAL;

 

AS400ではNULLは自動で0が補完されるようだ。SAL+COMMで、COMMがNULLでも0が入っている。

SELECT SAL , COM , SAL + COMM FROM HOGELIB.EMPLOYEES;

 

DISTINCTは使える。UNIQUEは使えない。

SELECT DISTINCT JOB FROM HOGELIB.EMPLOYEES;

 

複数列の組み合わせによる重複行の削除も使える。ただし並びが少しおかしい。

SELECT DISTINCT DEPTNO , JOB FROM HOGELIB.EMPLOYEES;

 

関数編

 

UPPER関数は使える。(ウチのASは小文字は使用してないので、意味ないけど)

SELECT YOMI,UPPER(YOMI) FROM EMPLOYEES;

LOWER関数も使える。

SELECT YOMI,LOWER(YOMI) FROM EMPLOYEES;

LOWER関数をWHERE句で使用した場合も使える。

SELECT YOMI FROM EMPLOYEES WHERE LOWER(YOMI) = ‘takahashi’;

UPPER関数とLOWER関数を文字リテラルに使用した場合も問題なし。

SELECT ‘small char’ , UPPER(‘small char’) FROM DUAL;

INITCAP(最初の文字を大文字にする関数)は使えない。oracleならOracleになる。

SELECT INITCAP(‘oracle’) FROM DUAL;

CONCAT(2つの文字列連結)は使える。Oracleと同じで連結は2つまで。

SELECT CONCAT(‘Oracle’,’Master’) FROM DUAL;

3つ以上の文字連結は||(パイプ)を使う。Oracleと同じ。

SELECT ‘Oracle’ || ‘Server’ || ‘Machine’ FROM DUAL;

SUBSTR関数。何文字目から何文字文の文字列を戻す。これも使える。下記の例はracになる。

SELECT SUBSTR(‘Oracle Server’,2,3) FROM DUAL;

SUBSTR関数、第3引数を省略すると、以降全てを戻す。下記の例はracle Serverになる。

SELECT SUBSTR(‘Oracle Server’,2) FROM DUAL;

SUBSTR関数、引数に負の値を指定すると、Oracleでは後ろから数えるが、DB2はエラーにはならないが表示もされない。

SELECT SUBSTR(‘Oracle Server’,-6,3) FROM DUAL;

LENGTH関数(引数として受け入れた文字列の文字数を戻す)は使える。下記の例は13。スペースも1文字。

SELECT LENGTH(‘Oracle Server’) FROM DUAL;

INSTR関数(指定した文字パターンが現れる位置を戻す)は使えない。

SELECT INSTR(‘Oracle Server’,’er’,1,2) FROM DUAL;

LPAD、RPAD関数(引数として受け入れた文字列がn文字になるように埋め込み文字を埋め込んで返す)も使えない。

SELECT RPAD(‘Oracle Server’,10,’*’) FROM DUAL;

REPLACE関数(第2引数の文字があった場合第3引数の文字に置き換える)は使える。

SELECT REPLACE(‘Oracle Server’,’Server’,’Master’) FROM DUAL;

TRIM関数は使えるようだ。LEADINGの場合。結果はracle Server。

SELECT TRIM(LEADING ‘O’ FROM ‘Oracle Server’) FROM DUAL;

TRIM関数、TRAILINGの場合。結果は、Oracle Serve。

SELECT TRIM(TRAILING ‘r’ FROM ‘Oracle Server’) FROM DUAL;

TRIM関数、BOTHを利用して全角スペースをトリムすることもできた。

SELECT TRIM(BOTH ‘ ’ FROM ‘ Oracle Master ’) FROM HAMLIB.DUAL;

TRIM関数、BOTHと削除文字を前後の半角スペースをトリムすることもできた。

SELECT TRIM (‘   Oracle Server   ‘) FROM DUAL;

LENGTHとTRIMをネストすることもできた。

SELECT LENGTH(‘   Oracle Server   ‘) , LENGTH(TRIM(‘   Oracle Server   ‘)) FROM DUAL;

先頭の0を表示するとき

和暦と月をつなげて、2801などとしたいとき、WRE || MONでは、281と表示される。OracleならTO_CHAR(MON,’00’)でいいが、AS400の場合は、DIGITS(MON)とする。ようするに、WRE || DIGITS(MON)とする。

 

 

制限及びソート編

 

等号(=)を使用した条件の指定

SELECT EMPNO,ENAME,DEPTNO FROM EMPLOYEES WHERE DEPTNO = 30;

WHERE句に文字または日付リテラルを使用する場合は ‘ でくくること。大文字小文字区別あり。

SELECT EMPNO,ENAME,DEPTNO FROM EMPLOYEES WHERE ENAME = ‘佐藤’;

条件に計算式を入れることもできる。

SELECT EMPNO,ENAME,SAL,SAL*12 FROM EMPLOYEES WHERE SAL * 12 < 3000000;

BETWEEN も NOT BETWEENも使える。

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

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

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

ORDER BYで列別名を指定できるが、””で指定した場合は、””で指定する。Oracleと同じ。

SELECT EMPNO,ENAME,SAL*12 “Annsal” FROM EMPLOYEES ORDER BY “Annsal”;

列の位置でもORDER BYを指定できる。

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

OFFSETは使えない。ので何行目からの指定が無理のようだ。ちなみにROWSでもROWでも無理。

SELECT EMPNO,ENAME,SAL FROM EMPLOYEES OFFSET 5 ROWS;

FETCH FIRST ROWS ONLYは使える。ので、何行分はOKのようだ。ONLY無しの指定は動かない。

SELECT EMPNO,ENAME,SAL FROM EMPLOYEES ORDER BY SAL DESC FETCH FIRST 3 ROWS ONLY;

FETCH NEXT ROWS ONLYは使えない。別にFIRSTでいいのだが、試してみた。

SELECT EMPNO,ENAME,SAL FROM EMPLOYEES ORDER BY SAL DESC FETCH NEXT 3 ROWS ONLY;

FETCH WITH TIESは使えないようだ。

SELECT EMPNO,ENAME,SAL FROM EMPLOYEES ORDER BY SAL DESC FETCH FIRST 3 ROWS WITH TIES;

FETCH FIRST 50 PERCENT ROWS ONLYは使えない。

SELECT EMPNO,ENAME,SAL FROM HAMLIB.EMPLOYEES ORDER BY SAL DESC FETCH FIRST 50 PERCENT ROWS ONLY;

NULLS FIRST | NULLS LASTは、使えない。テストデータにNULLの有る無し関係ないと思う。

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

 

 

 

 

 

 

 

スポンサーリンク




スポンサーリンク





投稿日:2017年7月7日 更新日:

執筆者: