BusinessObjects Board

SQL in Multisource universe in IDT...problems.

Hello Guys,
this is my first post and…beh thank you for everybody will have the patience to read my problem.

I am working currently in an IDT environment and I am trying to create some objects based on a pool of dates, in order to make these dates formatted in the right way to merge them with a calendar.

Here the table involved:

Calendar:
table created in MARIA DB. In IDT I have created an object called “Day”, which has this SQL where condition:

calendar.“Day” <=
CASE
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 12 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 24 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 36 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 48 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 60 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 72 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Last Year’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/01/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘This Year (full months only)’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Year To Date’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/%d’) as DATE)
END
AND
calendar.“Day” >
CASE
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 12 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 12 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 24 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 24 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 36 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 36 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 48 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 48 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 60 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 60 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 72 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 72 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Last Year’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/01/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 1 YEAR
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘This Year (full months only)’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/01/01’) as DATE)- INTERVAL 1 DAY)
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Year To Date’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/01/01’) as DATE)- INTERVAL 1 DAY)
END

I have another multi source universe with tables from Oracle and Maria DB. On one of the Maria DB table I am trying to apply the same logic you have read before, in the where condition, to my different date fields, for ex. I have a field called “Logged Date” or “Requested Date”. When I put the code in the where condition, like this:

@catalog(‘WINAPROACH’).“PUBLIC”.“ar_records”.“LOGGED_DATE” <=
CASE
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 12 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 24 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 36 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 48 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 60 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 72 Month’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Last Year’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/01/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘This Year (full months only)’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Year To Date’ THEN CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/%d’) as DATE)
END
AND
@catalog(‘WINAPROACH’).“PUBLIC”.“ar_records”.“LOGGED_DATE” >
CASE
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 12 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 12 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 24 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 24 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 36 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 36 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 48 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 48 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 60 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 60 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Rolling 72 Month’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/%m/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 72 MONTH
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Last Year’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/01/01’) as DATE)- INTERVAL 1 DAY)-INTERVAL 1 YEAR
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘This Year (full months only)’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/01/01’) as DATE)- INTERVAL 1 DAY)
WHEN @Prompt(‘Range: ‘,‘A’,{‘Rolling 72 Month’,‘Rolling 60 Month’,‘Rolling 48 Month’,‘Rolling 36 Month’,‘Rolling 24 Month’,‘Rolling 12 Month’,‘Last Year’,‘Year To Date’,‘This Year (full months only)’},Mono,Constrained,Not_Persistent)=‘Year To Date’ THEN (CAST(DATE_FORMAT(CURRENT_DATE ,’%Y/01/01’) as DATE)- INTERVAL 1 DAY)
END

I receive errors (see the attachment). I tried to create a derived table based on a “select * from previous table” (ar_records of my example), so essentially a copy of the original table, in DFX trying to use both options:

STANDARD SQL-92 and Database-Specific, but again, when I try to apply my logic, by using mysql/maria db functions, I receive error which are reminding me the SQL functions I am trying to use, do not exist, ex:

“Function ‘DATE_FORMAT’ is unknown”

Or I receive error as “Invalid Expression”

here the questions:
1)How is it possible that in IDT, I created a single source universe and the code pasted works, but the same code in multisource environment doesn’t work?
2) If I use SLQ-92 stabdard how can i replace function like “Date format”, “CAST”,“INTERVAL” etc?
3) If the mysql/maria db dialect is not accepted in IDT, how can I create the same conditions? FOr x. I need to create the first of the previous month, or the last day of the week etc…Is there any solution?

I am very lost and I do not know how to proceed…
2016_01_26_16_33_50_Information_Design_Tool.jpg
2016_01_26_16_45_24_Information_Design_Tool.jpg
2016_01_26_16_46_36_Information_Design_Tool.jpg


karmakoma (BOB member since 2016-01-26)

Welcome to B:bob:B!
My first thought would be Maria DB isn’t supported…have you checked to see if it is in the PAM?


Nick Daniels :uk: (BOB member since 2002-08-15)

Thank you for the Welcome.

Essentially it is supported “MySQL AB 5.6 MySQL C-API” on the 4.1 platform, but MARIA DB is Mysql under a different name…and we have used since now, with MySql ODBC connectors for Windows.
This issue seems to come only with the Multi-source universe. Any clue??


karmakoma (BOB member since 2016-01-26)

From my own experience - as soon as you choose multisource, your database-specific SQL goes out of the window. You can’t use oracle, sql server or anything that worked in a single-source. You’re stuck with SQL-92 code that (presumably) is the lowest common denominator that works with everything.

There are some SQL-92 multi-source IDT dates that work here (3rd post down): DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2 Have a play around with some of them and adapt for your needs.

Debbie


Debbie :uk: (BOB member since 2005-03-01)