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.