Thursday, June 14, 2012

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.

3 comments:

Christian Berg said...

Hehe. Yeah, a realy simple JFK headshot this one ;-)

C.

Me said...

Hi,

Nice post. Do you know if we can customize the calendar widget also? so my requirement is to show only < Current_Date values and grey out all the future dates from the calendar widget..is that possible?

Me said...

Hi,

Nice post. Do you know if we can customize the calendar widget also? so my requirement is to show only < Current_Date values and grey out all the future dates from the calendar widget..is that possible?