TRUNC Time Off Date in OBIEE
"How do I get a prompt to display the day, minus 3 hours?" was the question.
This was a dashboard prompt (calendar widget) with a default value that used the following SQL:
SELECT CURRENT_DATE
FROM "Subject Area"
OK, easy enough.
We just needed to add a predicate or WHERE clause. What would I compare it with?
OK, I have a date column (with the no time component), so I can use that. I also have to use
OBIEE (Logical) SQL. Gah. I wrote it in Oracle SQL first,
day_column = sysdate - ( 3 / 24 ). Wait, I have to TRUNC the date to compare it to a date or else I won't get anything back.
day_column = TRUNC( sysdate - ( 3 / 24 ) ). Better. I test it out and it does what I need it to do.
Now, to convert it to OBIEE SQL.
Instead of SYSDATE, I'll use CURRENT_DATE. Now I need to subtract 3 hours.
TIMESTAMPADD for that.
[nQSError: 22025] Function TimestampAdd is called with an incompatible type. (HY000)
Oops. How about CURRENT_TIMESTAMP? Yeah, that works. Here's what I have so far:
TIMESTAMPADD( SQL_TSI_HOUR, -3, CURRENT_TIMESTAMP )
That doesn't work either. Ah, the time component, need to get rid of that.
TRUNCATE? Nope, it's for numbers. Hmmm...wait, CAST! I'll just cast it to a date which should remove the time component.
CAST( TIMESTAMPADD( SQL_TSI_HOUR, -13, CURRENT_TIMESTAMP ) AS DATE )
Awesome. The final logical SQL looks like this:
SELECT CURRENT_DATE
FROM "Subject Area"
WHERE
( ( "Date"."Date" IN ( CAST( TIMESTAMPADD( SQL_TSI_HOUR, -13, CURRENT_TIMESTAMP ) AS DATE ) ) ) )
Easy.
Labels: howto, obiee, sql
