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…
karmakoma (BOB member since 2016-01-26)