OracleでDISTINCTを追加したらORA-01791が発生【解決】

■　重複の除去

データを抽出して一覧を表示をする画面で設計ミスがあったらしく、表示結果が重複表示されるケースが出てきました。

調べてみると原因は分かったのですが、それを直そうと思ったら結構面倒なSQLの書き換えが必要になってしまいます。そこで、DISTINCTを追加して抽出結果から重複を除去することにしました。

ところが、実際にSQL文を実行したところ、ORA-01791（SELECT式が無効です）が発生して異常終了してしまいました。

■　原因

調べてみると、何でもソート順に指定している項目が抽出項目になっていない場合に発生するそうです。

例えば、下記のような書き方をすると同じエラーが発生します。

SELECT DISTINCT
       ITEM1
  FROM (
          SELECT 'A' AS ITEM1
                ,'B' AS ITEM2
            FROM DUAL
           UNION ALL
          SELECT 'A' AS ITEM1
                ,'B' AS ITEM2
            FROM DUAL 
       )
ORDER BY
       ITEM1
      ,ITEM2

ITEM2という項目がORDER BY句にはあるが、SELECT句にはありません。こういう書き方はダメってことらしいです。

■　対応

手っ取り早い対応は、SELECT句にORDER BY句で指定した項目を追加することでしょうか。上記のサンプルの場合、下記のようにITEM2を追加することでSQLが通るようになりました。

SELECT DISTINCT
       ITEM1
      ,ITEM2
  FROM (
          SELECT 'A' AS ITEM1
                ,'B' AS ITEM2
            FROM DUAL
           UNION ALL
          SELECT 'A' AS ITEM1
                ,'B' AS ITEM2
            FROM DUAL 
       )
ORDER BY
       ITEM1
      ,ITEM2

後は、逆にORDER BYからITEM2を消すという方法もあるかと思います。そのソート条件、本当に必要なのって業務的な確認は必要ですけどね。

なお、DISTINCTを消してGROUP BY句で重複を除去とかやってもダメで、ORA-00979（GROUP BYの式ではありません。）が発生します。

■　後書き

テーブルを結合した結果、抽出結果が重複するというのは良くある話です。その対応方法も色々とあるのですが、私が行ったDISTINCTで重複を除去するというのは邪道かと思っています。

ただ、今回の場合は抽出結果を画面上に表示するだけで、そこから何か（更新処理とか）に連携するというものでもなかったので、別に良いかなと。そもそも、ソート順に指定する項目が抽出結果にないっていう書き方の時点で、ねぇ。

投稿記事の一覧：http://harikofu.web.fc2.com/

--- blog end ---