MySQLのSQLをOracleで書く(Format,Date_Format)

移植するSQL

SELECT 
  FORMAT(item_fee, 0) AS item_fee      
, item_cd       
, item_nm AS item_nm       
, SUM(item_stock) AS stock       
, DATE_FORMAT(MIN(arrival_ymd), '%Y/%m/%d') AS arrival_ymd 
FROM item
GROUP BY item_fee, item_cd 

MySQLで使用されていたSQL。これをOracleでそのまま利用すると次のエラーが発生した。

エラー１

ORA-00904: "FORMAT"
ORA-00904: "DATE_FORMAT"

エラーの原因はMySQLでは使用できるFORMAT関数およびDATE_FORMATだがOracleには存在しないことが原因。
よってTO_CHARに書き換えることで対応。
FORMAT(item_fee, 0)は数値型を小数点0桁目（つまり整数）で丸めて文字列にする。よって引数を省略しデフォルト引数で対応した。
また、TO_DATE引数について %YなどはOracleでは使えないため"YYYY/MM/DD"表記に変更。
TO_CHARについては下記リンクを参考にした。
https://segakuin.com/oracle/function/to_char.html

SELECT 
  TO_CHAR(item_fee) AS item_fee      
, item_cd      
, item_nm AS item_nm       
, SUM(item_stock) AS stock        
, TO_CHAR(MIN(arrival_ymd), 'YYYY/MM/DD') AS arrival_ymd 
FROM item
GROUP BY item_fee, item_cd 

エラー２

上記のSQLを実行すると

ORA-00979: GROUP BYの式ではありません。

のエラーが発生した。SELECT句とGROUP BY 句を確認すると…

SELECT 
  TO_CHAR(item_fee) AS item_fee      
, item_cd      
, item_nm AS item_nm       
, SUM(item_stock) AS stock        
, TO_CHAR(MIN(arrival_ymd), 'YYYY/MM/DD') AS arrival_ymd 

GROUP BY item_fee, item_cd 

SELECTで取得している未集計のカラムとGROUP BY句のカラムがあっていない。item_nmがGROUP BY句に必要なはずである。
どうやらMySQLではGROUP BYのカラムとSELECTのカラムが違ってもエラーにならないらしい。（何故…？？）
https://qiita.com/kazu56/items/c27428ddde49be42f23b
よってGROUP BY句を修正して実行した。

SELECT 
  TO_CHAR(item_fee) AS item_fee      
, item_cd      
, item_nm AS item_nm       
, SUM(item_stock) AS stock        
, TO_CHAR(MIN(arrival_ymd), 'YYYY/MM/DD') AS arrival_ymd 
FROM item
GROUP BY item_fee, item_cd, item_nm 

エラー３

上記のSQLを実行すると

ORA-01481: 数値書式モデルが無効です

のエラーが発生した。
原因はarrival_ymdの取得の部分であった。MINで取得された文字列をもう一度TO_CHARで文字列に変換しようとしていたため引数にエラーが出ていたよう。一度取得した文字列を日付型に変換してからTO_CHARでフォーマットを指定しつつ文字列に変換する。

SELECT 
  TO_CHAR(item_fee) AS item_fee      
, item_cd      
, item_nm AS item_nm       
, SUM(item_stock) AS stock        
, TO_CHAR(TO_DATE(MIN(fio.pick_ymd)), 'YYYY/MM/DD') AS pick_ymd 
FROM item
GROUP BY item_fee, item_cd, item_nm 

これを実行すると…

ついにデータが取得できた！

まとめ

今回行ったこととしては、
①FORMAT関数およびDATE_FORMATをTO_CHARに書き換え
②SELECT句とGROUP BY 句の確認
③MINとTO_CHARの使用方法
であった。
おそらくはじめてのMySQLからOracleへのSQL移植だったがつまずくポイントがいくつかあった。
OracleとMySQLでは使える文法や引数が違うものはよくあるようなので気を付けていきたい。