Hello All,
I have built a multi source universe with all the databases in Oracle. I am not able to manipulate dates e.g. I have to create several objects using date_diff, add_months etc. I am not able to subtract one date from another and from sysdate. Any help is appreciated.
Thanks.
Purnima
prnmsharma 
(BOB member since 2008-03-18)
Does it work outside of BO or are you asking for help with your SQL?
Nick Daniels 
(BOB member since 2002-08-15)
If you’re using multisource, you probably need to special date codes from here: DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2
Debbie
Debbie (BOB member since 2005-03-01)
Using the multisource syntax is usually the best way to go, but it’s possible to use the native syntax of the underlying database as well.
This is accomplished with the undocumented NATIVE EXPRESSION function. Its syntax is:
NATIVE EXPRESSION("qualifier",<output datatype>,'<native function call>',<parameter 1>,<parameter 2>,...)Any single quotes in the native function must be converted to double single-quotes.
For example, to truncate a specific column using Oracle syntax.
NATIVE EXPRESSION("my_db",TIMESTAMP,'trunc(my_table.my_date,''dd'')')Parameters can be used to pass run-time values. For example, call database function and pass a prompt value:
NATIVE EPXRESSION("my_db",INTEGER,'myfunction(?)',@Prompt('Start Date'))There are limitations, but it seems to work.
Using the Data Federation tool is essential for debugging, since it shows the SQL that is passed to the underlying database.
Joe
joepeters (BOB member since 2002-08-29)