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