Null DATE Value gets populated as TO_DATE('1900-01-01' , 'YYYY-MM-DD') in OBIEE SQL!!! — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Null DATE Value gets populated as TO_DATE('1900-01-01' , 'YYYY-MM-DD') in OBIEE SQL!!!

Received Response
2479
Views
4
Comments
2753634
2753634 Rank 3 - Community Apprentice

Hello Everyone,

I have a very strange situation here.

I have 2 columns COP Date and Deactivation Date both of which are DATE type in Physical table.

When I map these directly tot he analysis and run the report in OBIEE,

the expected SQL is

select COP Date as c1,

     Deactivation Date as c2,

from ....

Union All

select null as c1,

     null as c2,


Instead I get it as

select COP Date as c1,

     Deactivation Date as c2,

from ....

Union All

select TO_DATE('1900-01-01' , 'YYYY-MM-DD') as c1,

     TO_DATE('1900-01-01' , 'YYYY-MM-DD') as c2,

from ......

Can any one please help me as to how I get this resolved...

Thanks,

Nikhila.


Answers

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Are you using physical or logical query?  if you are using logical SQL, check TO_DATETIME('2009-03-03 01:01:00', 'yyyy-mm-dd hh:mi:ss')  TO_DATETIME('2009-03-03’, 'yyyy-mm-dd')

  • 2753634
    2753634 Rank 3 - Community Apprentice

    I was referring to Physical SQL.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Cesar is implyimg what we are all thinking:

    There some logic hidden somewhere which forces this change. Check all config options of both columns throughout the RPD.

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Check the "Features" of your database, also check the connection pool if you are using any special script and tell us about it and what type of database are you using,

    Check this sample of oracle 11g database, there is no problem about using a physical SQL against the connection the pool.

    Screen Shot 2016-10-25 at 11.24.54 PM.png

    Screen Shot 2016-10-26 at 12.39.31 AM.png

    Screen Shot 2016-10-25 at 11.45.56 PM.png

    Screen Shot 2016-10-25 at 11.11.46 PM.png

    Screen Shot 2016-10-25 at 11.13.27 PM.png