The physical layer shows this as a DATETIME column which I changed to DATE since I am only interested in the date part.
No. Bad idea. The physical layer of the RPD should show what is in the physical source. So if it's not a DATE, don't pretend to OBIEE that it is. For the exact reason that you're hitting now - you're expecting OBIEE to write some magic SQL to cast the datatype, without even telling it what the original type is. OBIEE won't query the metadata to check on data types in the source, it will rely on what you tell it in the physical layer.
How can I achieve this at the RPD level?
You could use an inline view in the physical layer and do your trunc there I guess. Better, as you've already identified, is to store the date as a date if that's going to be a key column on which to join.
I want to know why even if I selected DATE as the datatype in the RPD, the join created does not do a trunc.
See above. OBIEE does not know it's a DATETIME, so why would it do a TRUNC?
Valid points Robin as always. I have already initiated action to have a truncated date field added to the table.
But...I don't get your point that OBIEE doesn't know it's DATETIME....but it does...since the RPD shows it's data type as DATETIME by default AND it allows me to change it to DATE. From a users perspective (my), doesn't it mean that a data type conversion is implied by my action of changing DATETIME to DATE? That is why I was expecting a trunc.
1 person found this helpful
OBIEE doesn't know it's DATETIME because you changed it to DATE. The type of the column in the physical layer is not a transformation parameter (you have datetime in the DB and you want a date in OBIEE), it's a definition parameter: you tell OBIEE that the column is DATE. This parameter tell OBIEE what it can do or not on that column and what will you be allowed to do or not with the column, but it doesn't generate any implicit transformation (the expected TRUNC you didn't get) on the column.
So when you change the type from DATETIME to DATE you are "cheating" telling OBIEE a wrong information, but you aren't asking the tool to transform your DATETIME into a DATE. You are just changing the behavior of functions and available operations on the columns and it's up to you, the user, to know what you are doing.
And you see the impact of your cheat: you did a join between 2 columns defined as DATE and OBIEE didn't complained at all, but the behavior is not what you expect because for OBIEE 2 date columns are just matched using the = operator . But you gave a wrong information to OBIEE, so your match almost never works because one of the 2 columns contains time information.
Ah... the light bulb went on!
Now that you mention it, I realized that unlike the Oracle data dictionary, OBIEE does not remember that it's a DATETIME field. So when I changed to DATE, it said "ok date it is" and went it's merry way.
Thanks for clarifying.