Hi all,

I have a weird scenario.

Sql statement with CASE failed when using DB link ,but run well locally:

Code:
SQL> conn bi@bscs_rep
Enter password: *******
Connected.

SQL> Select contract_id, factor, seniority
  2  From dw.VW_LOLY_FACTOR_FOR_CONTRACTS@dwprod
  3  Where data_Date = 
  4  case when to_date('14112007', 'DDMMYYYY') 
  5  in ( select distinct data_date from dw.VW_LOLY_FACTOR_FOR_CONTRACTS@dwprod )
  6  then to_date('07112007', 'DDMMYYYY') else
  7  (select max(data_date ) from dw.VW_LOLY_FACTOR_FOR_CONTRACTS@dwprod ) end;
(select max(data_date ) from dw.VW_LOLY_FACTOR_FOR_CONTRACTS@dwprod ) end
        *

ERROR at line 7:

ORA-00932: inconsistent datatypes

SQL> select db_link,HOST,OWNER,USERNAME
  2  from all_db_links
  3  where db_link like '%DWPROD%';

DB_LINK                HOST       OWNER      USERNAME

---------------------- ---------- ---------- ----------------------
DWPROD.WORLD           DWDEV      PUBLIC     BILL_GUEST

connecting to the remote database and run locally the statement:

Code:
SQL> conn bill_guest@dwdev
Enter password: **********
Connected.

 
SQL> Select contract_id, factor, seniority
  2  From dw.VW_LOLY_FACTOR_FOR_CONTRACTS
  3  Where data_Date = 
  4  case when to_date('14112007', 'DDMMYYYY') 
  5  in ( select distinct data_date from dw.VW_LOLY_FACTOR_FOR_CONTRACTS )
  6  then to_date('07112007', 'DDMMYYYY') else
  7  (select max(data_date ) from dw.VW_LOLY_FACTOR_FOR_CONTRACTS ) end;

 
CONTRACT_ID     FACTOR  SENIORITY

----------- ---------- ----------
    5254784        .02         49
    8745201        .02         60
    9853209        .02         65
    5219854        .02         70
    6512087        .02         70

  
……
It runs well ...
Why?

Thanks in advance,
Nir