Set number in filter works fast, formula takes much longer?

[JMoney RRD]

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?

[rockytopmark]

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

[JMoney RRD]

The data type says "Packed Decimal".

[Deep750]

Shouldn't matter, but you can try to do something like
Date = to_number(to_char(sysdate; 'yyyymmdd'))
This is with oracle database

[JMoney RRD]

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!

[sjdig]

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?

Code Select Expand

[Date] = current_date

Maybe I missed something regarding why it wouldn't have been possible in the previous posts.

[JMoney RRD]

Only because our dates are stored as a number like yyyymmdd. So I have to convert current_date to a yyyymmdd format like 20180109.

[bdbits]

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.

[JMoney RRD]

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.