CAST null AS DATE gives 1-1-1900 date instead of null
Hi,
I'm defining a Logical Table with OBIEE version 10.1.3.4.1 that maps tables on Oracle Database 11.1.0.6.0.
This logical table maps two LTS and contains a date column that has a value only for 1 of the 2 LTS.
Mapping for LTS 1 is:
CASE IFNULL("JDE Data Source".""."PRODDTA"."F5503001"."BHDDJ", 0) WHEN 0 THEN CAST ( NULL AS DATE ) ELSE EVALUATE('TO_DATE(%1,%2)' AS DATE , CAST ("JDE Data Source".""."PRODDTA"."F5503001"."BHDDJ" + 1900000 AS CHARACTER ( 7 )), 'YYYYDDD') END
Mapping for LTS2 is:
CAST ( NULL AS DATE )
When I create a report that uses this column, OBIEE does not generate the correct query:
select distinct D2.c1 as c1,
D2.c2 as c2
from
((select 'TIT' as c1,
case nvl(T35695.BHDDJ , 0) when 0 then cast(NULL as DATE) else TO_DATE(cast(T35695.BHDDJ + 1900000 as CHARACTER ( 7 ) ),'YYYYDDD') end as c2
from
PRODDTA.F5503001 T35695
union all
select cast(T29763.RPDCT as CHARACTER ( 5 ) ) as c1,
TO_DATE('1900-01-01' , 'YYYY-MM-DD') as c2
from
PRODDTA.F03B11 T29763
where ( T29763.RPAAP <> 0 ) )
) D2
order by c1, c2
The column is mapped with TO_DATE('1900-01-01' , 'YYYY-MM-DD') expression, not cast(NULL as DATE) as specified in the RPD.
Did anyone come across this kind of issue? Have you any suggestion on how to obtain the correct result?
Thanks a lot and regards,
Cristina