Hi,
I have a report that always run on today's date in a yyyymmdd format.
If I type in the number like Date = 20180108 the report runs within seconds.
If I use a formula in the filter to generate that date the report takes 8-10 minutes. Currently the formula is Date = extract(year,current_date)*10000+extract(month,current_date)*100+extract(day,current_date).
It works, but takes much longer.
There something I can do to speed that up like it is when the number is set?
Quote from: JMoney RRD on 08 Jan 2018 04:01:25 PM
Hi,
I have a report that always run on today's date in a yyyymmdd format.
If I type in the number like Date = 20180108 the report runs within seconds.
If I use a formula in the filter to generate that date the report takes 8-10 minutes. Currently the formula is Date = extract(year,current_date)*10000+extract(month,current_date)*100+extract(day,current_date).
It works, but takes much longer.
There something I can do to speed that up like it is when the number is set?
What is the data type of the query item being compared to 20180108? If it trully is numeric, then it shouldn't matter...
Sent from my SM-G950U1 using Tapatalk
The data type says "Packed Decimal".
Shouldn't matter, but you can try to do something like
Date = to_number(to_char(sysdate; 'yyyymmdd'))
This is with oracle database
What I ended up doing and so far appears to work is just set the filter to maximum(Date) in a query and use that to filter the others. Since maximum(Date) will always be today in that data. so far so good. Thanks for the assistance though still learned a few things!
I realize you've already found a solution that works at this point.
However, is there any reason you couldn't have just used the following?
[Date] = current_date
Maybe I missed something regarding why it wouldn't have been possible in the previous posts.
Only because our dates are stored as a number like yyyymmdd. So I have to convert current_date to a yyyymmdd format like 20180109.
If at all possible, dates should be stored as dates. (It smacks of DB2 or a mainframe and I suspect it is not possible for you.) There is a whole slew of useful functionality you are giving up using numbers instead.
Quote from: bdbits on 09 Jan 2018 04:56:00 PM
If at all possible, dates should be stored as dates. (It smacks of DB2 or a mainframe and I suspect it is not possible for you.) There is a whole slew of useful functionality you are giving up using numbers instead.
Yeah I agree, and also correct I can't do anything to change it :( Works for very large corporation and this is how they have always had it setup.