An issue with the left JOIN association that Oracle uses when using case in query results cannot be judged
Query method One:
1 SELECT 2 Case when(SELECT CAST(SUM( Case3 when(Allocable_prime_currency_value is NULL andState_ind= 1) Then4NVL (Prime_currency_value,0)5 ELSE6NVL (Allocable_prime_currency_value,0)END) as Number( -,6)) asAllocable_prime_currency_value7 fromRp_ledger_item R8 WHEREr.source_id=rp_cash_movement. cash_movement_id9 andSource_type= 'CASH')= 0 Then 0 ELSE 1 END,Ten(SELECT SUM( Case One when(Allocable_prime_currency_value is NULL andState_ind= 1) Then ANVL (Prime_currency_value,0) - ELSE -NVL (Allocable_prime_currency_value,0)END) asAllocable_prime_currency_value the fromRp_ledger_item R - WHEREr.source_id=rp_cash_movement. cash_movement_id - andSource_type= 'CASH'), - Rp_ledger_item. ledger_item_id + fromrp_cash_movement - Left JOINRp_ledger_item + onRp_ledger_item. cash_movement_id=rp_cash_movement. cash_movement_id A andRp_ledger_item. cash_movement_id is not NULL at WHERENVL (Rp_cash_movement.is_reserve_fund,'0')= '0' - and 1 = 1 - andRp_cash_movement. rp_id= 'R' - andRp_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')
The query results are as follows:
Query method Two:
SELECT Case when(SELECT CAST(SUM( Case when(Allocable_prime_currency_value is NULL andState_ind= 1) ThenNVL (Prime_currency_value,0)ELSENVL (Allocable_prime_currency_value,0)END) as Number( -,6)) asAllocable_prime_currency_value fromRp_ledger_item RWHEREr.source_id=rp_cash_movement. cash_movement_id andSource_type= 'CASH')= 0 Then 0 ELSE 1 END,(SELECT SUM( Case when(Allocable_prime_currency_value is NULL andState_ind= 1) ThenNVL (Prime_currency_value,0)ELSENVL (Allocable_prime_currency_value,0)END) asAllocable_prime_currency_value fromRp_ledger_item RWHEREr.source_id=rp_cash_movement. cash_movement_id andSource_type= 'CASH'),"'ledger_item_id fromrp_cash_movement Left JOINRp_ledger_item onRp_ledger_item. cash_movement_id=rp_cash_movement. cash_movement_id andRp_ledger_item. cash_movement_id is not NULLWHERENVL (Rp_cash_movement.is_reserve_fund,'0')= '0' and 1 = 1 andRp_cash_movement. rp_id= 'R' andRp_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')
Query results
The biggest difference between 2 SQL statements is that
Some may not express it clearly,
The main reason I do not know, if anyone interested can reply to me!