I need to add some Date dimension in IDT (Information Design Tool 4.2 version ©2010 - 2016 SAP SE., Build: 14.2.3.v20170913-2538)
This dimension must be used indepent of the rest of dimension.
I try 2 ways:
SELECT trunc(sysdate ,'IW') as "First Day of Week",trunc(sysdate,'MM') as "First Day of Month ", trunc(sysdate ,'YYYY') as "First Day of Year",trunc(add_months(sysdate,-1),'MM') "first Day of Month-1", add_months(sysdate,-1) as "Date of Month-1 ",trunc (sysdate-6, 'IW') "First Day of Week-1" , to_date(to_char(sysdate,'YYYY')-1||'/'||to_char(sysdate,'MM/DD'),'YYYY/MM/DD') AS "Year-1",to_date(to_char(sysdate,'YYYY')-1||'/01/01','YYYY/MM/DD') AS "First Day of Last Year"FROM Dual
But when I want add this dimension with other dimension, I have an error in "You cannot run this query because it will produce a Cartesian product(IES 00012)"
I know in UDT, function last_day, add_months exist, but it's not the case in IDT.