ITスキル

Oracleエラー「ORA-01417」を解説。先にテーブルを結合しておきましょう

Oracleエラー「ORA-01417」について、わかりやすく解説します。

ORA-01417とは

ORA-01417は、外部結合を用いたSELECT文を書いているときに出てくるエラーです。
日本語メッセージは
「表が少なくとも1つの他の表に外部結合されている可能性があります」
ちょっと意味わかりませんよね…

「外部結合」自体がとっつきにくい概念なのに、独特な日本語でさらにむずかしい。
エラー内容をわかりやすく言いかえると
「3つ以上の表で外部結合を作らないでね」
「外部結合するテーブルの親はひとつだけだよ」あたり。

エラーの原因

外部結合については別記事で解説するとして、ORA-01417が発生してしまう理由を説明します。

データベース例:
従業員の必殺技
をOracleデータベースで管理している普通の会社があるとします。
構成テーブルは、なんの変哲もない従業員マスタ(A)と、従業員番号と必殺技の関連マスタ(B)、そして各必殺技を管理するマスタ(C)です。
(ここでいう必殺技は、ジョジョのスタンドのようにユニークなものではなく、ドラクエの魔法のように共通のものとお考えください)

このデータベースを用いて
「従業員情報に必殺技の情報を関連付けて表示したい。
ひとつも必殺技を持っていない従業員情報も表示したい」
という要望が出たとします。
このようにテーブル同士が必ずしも結合されるとは限らない場合は 外部結合を使います。しかし、以下の使い方はNG。ORA-01417エラーが発生します。

SELECT *
FROM 従業員マスタ A
, 従業員_必殺技 B
, 必殺技マスタ C
WHERE A.従業員番号 = B.従業員番号(+)
AND C.必殺技コード = B.必殺技コード(+)

なぜなら、外部結合として使っているBテーブルの親がAとCふたつになってしまうからです。

Bテーブルは、Aテーブルの従業員番号と一致するものだけ表示してね!
Bテーブルは、Cテーブルの必殺技コードと一致するものだけ表示してね!
という状態になると、Oracleはコレをエラーとして認識します。外部結合じゃなければ普通に通るんですけどね。まあ、そういうものだから仕方がないです。

解決策

解決策は至ってシンプルです。
外部結合されるテーブルに対し、関連テーブルを1つにすればいいのです。

SELECT *
FROM (SELECT A.*, B.必殺技コード
FROM 従業員マスタ A, 従業員_必殺技 B
WHERE A.従業員番号 = B.従業員番号(+) ) X
, 必殺技マスタ C
WHERE X.必殺技コード = C.必殺技コード(+)

こんな感じで、AとBをひとつにまとめたテーブルXを作っておいて、それに対してCを外部結合すればいいのです。

(AとB)とC としていますが、Aと(BとC)でもいいです。こっちの方がすっきりしますかね。

総括

上記サンプルだと、必殺技をたくさん持っている従業員は大量にレコードが表示されますね。選定をミスりました。ごめんなさい。
なお、従業員1名あたりの必殺技数に上限がある場合は、横持ちにすることも効果ありです。

ちなみに、このエラーの原文(英語)は
「a table may be outer joined to at most one other table」です。
日本語のエラーはこれを直訳した感じです。

Cannotとか、Don'tとか、否定文を入れてくれればマシな気もするんですが…。

お役に立ちますと幸いです。
あくまで個人の経験、主観で書いていますので、細かい誤りなどありましたら教えていただけますと幸いです。

お読みいただきありがとうございました。

COMMENT

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください