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
KScope 2012 Advanced Registration Ends June 9th!
It's almost here.
Kscope 2012. Just 18 more days.
What's that? You're not going?
Need to justify the trip? Check out the bottom of the registration page
here. There are some helpful hints for Developers, Managers and BI/EPM Professionals.
On June 9th, Advance Registration ends. If you hurry, you can save $300. Go now.
If you do sign up in the next three days AND show proof of such, I'll bring you an ORACLENERD T-Shirt (white on black, the classic). Limit 5. Donations always accepted (joke, I won't make you donate...unless I've had a beer, then I'll maybe hassle you for a few minutes, but nothing too harsh, I promise).
Labels: 2012, kscope, odtug
