BusinessObjects Board

SQL not parsed as before? why am I getting this error?

building semantic layer
#1

see attached screenshots. The SQL validates in the SQL Assistant and returns values properly when pasted into SQL*Plus. Database used - Oracle V11
any ideas why the report errors because of this? I have used this same SQL statement successfully in filters in previous (non IDT) universes (.unv)

EDIT I would post screenshots if it lets me (rejecting my 120kB .png files ). SQL filter is :

PER_DATE=trunc(to_date('01-'||substr(add_months(trunc(sysdate),-1),4,6),'DD-MON-RR'))

and the error encountered when trying to use that filter is

[Moderator Edit: Added code formatting, etc. - Andreas]

spoons :uk: (BOB member since 2012-06-26)

#2

What does RR represent? It looks like you’re trying to substring a date?

#3

DD-MON-RR is a fairly standard date format in Oracle. It is the last 2 digits of the year but with an implicit “20” in front, came into being at the end of the last century (year 2k etc). Now we are in 2012 it’s probably safe to start using DD-MON-YY again as I doubt much comparison of dates prior to 2000 versus 21st centiry dates now occurs but old habits die hard :wink:

Correct, using SUBSTR to find the “MON-RR” portion of the date for last month (add_months minus 1 will give us last month but with the same day number), then appending “01-” to the front, then converting the whole string to a date in the format DD-MON-RR (01-OCT-12)

It’s moot as have now solved this - have used the more efficient method from the sticky:
trunc((trunc(sysdate,‘MM’)-1),‘MM’)

rather than my old fashioned method of using to_date as this is a date format understanding problem between bus obs and the database.

That neatly side steps the problem.

As a note of interest, anyone know where you can define / configure a default date format for a universe in bus obs??

spoons :uk: (BOB member since 2012-06-26)

#4

If you were looking at relative dates, this thread would be helpful :slight_smile:

#5

yup that was the one I looked at from the sticky

spoons :uk: (BOB member since 2012-06-26)