問題の概要

OracleでLEFT JOINを使用して関連付けたテーブルに対し、CASE WHEN式を用いた条件分岐が期待通りに動作しない場合があります。本稿では、具体的なクエリ例を基にこの現象について説明します。

問題のあるクエリ例

以下のクエリは、現金移動テーブル（RP_CASH_MOVEMENT）と台帳アイテムテーブル（RP_LEDGER_ITEM）をLEFT JOINで結合し、集計結果を判定しています。

SELECT 
  CASE WHEN (
    SELECT CAST(SUM(
      CASE
        WHEN (ALLOCABLE_PRIME_CURRENCY_VALUE IS NULL AND STATE_IND = 1) THEN
          NVL(PRIME_CURRENCY_VALUE, 0)
        ELSE
          NVL(ALLOCABLE_PRIME_CURRENCY_VALUE, 0)
      END
    ) AS NUMBER(18,6))
    FROM RP_LEDGER_ITEM R
    WHERE R.SOURCE_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
      AND SOURCE_TYPE = 'CASH'
  ) = 0 THEN 0 ELSE 1 END AS CHECK_FLAG,
  (
    SELECT SUM(
      CASE
        WHEN (ALLOCABLE_PRIME_CURRENCY_VALUE IS NULL AND STATE_IND = 1) THEN
          NVL(PRIME_CURRENCY_VALUE, 0)
        ELSE
          NVL(ALLOCABLE_PRIME_CURRENCY_VALUE, 0)
      END
    )
    FROM RP_LEDGER_ITEM R
    WHERE R.SOURCE_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
      AND SOURCE_TYPE = 'CASH'
  ) AS TOTAL_VALUE,
  RP_LEDGER_ITEM.LEDGER_ITEM_ID 
FROM RP_CASH_MOVEMENT
LEFT JOIN RP_LEDGER_ITEM
  ON RP_LEDGER_ITEM.CASH_MOVEMENT_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
WHERE NVL(RP_CASH_MOVEMENT.IS_RESERVE_FUND, '0') = '0'
  AND RP_CASH_MOVEMENT.RP_ID = 'R'
  AND RP_CASH_MOVEMENT.INPUT_DATE >= TO_DATE('2014/2/1 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND RP_CASH_MOVEMENT.OFFICE_ID = '0B4B12XOG33MO'

修正版のクエリ

次のクエリでは、LEDGER_ITEM_ID列を空文字で代用しています。

SELECT 
  CASE WHEN (
    SELECT CAST(SUM(
      CASE
        WHEN (ALLOCABLE_PRIME_CURRENCY_VALUE IS NULL AND STATE_IND = 1) THEN
          NVL(PRIME_CURRENCY_VALUE, 0)
        ELSE
          NVL(ALLOCABLE_PRIME_CURRENCY_VALUE, 0)
      END
    ) AS NUMBER(18,6))
    FROM RP_LEDGER_ITEM R
    WHERE R.SOURCE_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
      AND SOURCE_TYPE = 'CASH'
  ) = 0 THEN 0 ELSE 1 END AS CHECK_FLAG,
  (
    SELECT SUM(
      CASE
        WHEN (ALLOCABLE_PRIME_CURRENCY_VALUE IS NULL AND STATE_IND = 1) THEN
          NVL(PRIME_CURRENCY_VALUE, 0)
        ELSE
          NVL(ALLOCABLE_PRIME_CURRENCY_VALUE, 0)
      END
    )
    FROM RP_LEDGER_ITEM R
    WHERE R.SOURCE_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
      AND SOURCE_TYPE = 'CASH'
  ) AS TOTAL_VALUE,
  '' AS LEDGER_ITEM_ID 
FROM RP_CASH_MOVEMENT
LEFT JOIN RP_LEDGER_ITEM
  ON RP_LEDGER_ITEM.CASH_MOVEMENT_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
WHERE NVL(RP_CASH_MOVEMENT.IS_RESERVE_FUND, '0') = '0'
  AND RP_CASH_MOVEMENT.RP_ID = 'R'
  AND RP_CASH_MOVEMENT.INPUT_DATE >= TO_DATE('2014/2/1 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND RP_CASH_MOVEMENT.OFFICE_ID = '0B4B12XOG33MO'

両者の相違点

上記の2つのクエリの主な違いは最后のSELECT句にあります:

• 問題のあるクエリ: RP_LEDGER_ITEM.LEDGER_ITEM_ID を直接参照
• 修正版: '' AS LEDGER_ITEM_ID として空文字を代入

原因の考察

この問題の発生にはいくつかの要因が考えられます:

1. LEFT JOINとスカラサブクエルの相互作用: LEFT JOINを行うことで、結合されたテーブルの列がNULL値を取る可能性があります。これにより、サブクエリ内のCASE WHEN式の評価に影響を与える可能性があります。
2. Oracleの最適化動作: データベースのオプティマイザがクエリ план を最適化する 과정에서、LEFT JOINされた列の参照方法によって結果が異なる場合があります。
3. NULL値の伝播: LEFT JOINで一致する行がない場合、結合されたテーブルの列にはNULLが設定されます。このNULL値がCASE WHENの評価に影響を与えている可能性があります。

정확한根本原因を特定するには、执行計画（EXPLAIN PLAN）の詳細な分析が必要です。実際の環境での検証をお勧めします。