You can translate the question and the replies:

Filtering between Current_Date and Current_Date - x days

I'm looking to filter a query by dates. The date (my_dt) within the table I'm pulling from is in the format yyyyMMdd but as type numeric and length of 8. So, what I'm trying to do is filter where my_dt is between a date that is x days ago from today and today (in this case, assume x is 5). So here is what I come up with: First Variation: where my_dt between cast(formatdate('yyyyMMdd',(addday(current_date(),-5))) as numeric) and cast(formatdate('yyyyMMdd',current_date()) as numeric) Second Variation: where my_dt >= cast(formatdate('yyyyMMdd',(addday(current_date(),-5))) as numeric) and my_dt <= cast(formatdate('yyyyMMdd',current_date()) as numeric) The problem is, when I try the above where conditions, the query just spins and spins forever and eventually times out, I have never been able to run it successfully. I am only pulling 2 fields in the select statement. However, if I do the following, it runs quite quickly. I don't understand why because it seems like essentially the same thing. where my_dt >= 20180816 and my_dt <= 20180822, Note: I can do this in multiple tools selecting from dual() and it will return the result of my datefunctions/cast on current_date, it's just that for some reason the filter has trouble with it. select cast(formatdate('yyyyMMdd',(addday(current_date(),-5))) as numeric) as five_days_ago, cast(formatdate('yyyyMMdd',current_date()) as numeric) as today from dual() I've even tried converting my_dt to a date instead like this but hasn't worked: where to_date('yyyyMMdd',my_dt) >= addday(current_date(),-5) and to_date('yyyyMMdd',my_dt) <= current_date()
22-08-2018 14:57:02 -0400

1 Answer

Hi, One way to get some information about what’s going on would be to get the query plan using [desc queryplan]( followed by the query you’re running. This should give you information on where things are being delegated to, if there are more functions you can delegate, and that the sql generated by denodo is accurate to what you’re trying to do. You can take that query from the bottom node of the query plan, and run it directly on the source to help confirm that it is the query performance producing problems. To get it working properly, if none of the dates you’re comparing are future dated, we can eliminate about half of the processing load by eliminating the <= current_date(). You’ll also want to create an index on the base view containing the two fields, indexing on those two fields, and ordered by descending dates (most sql engines start at the top and work down, so this will minimize the number of rows you’re processing, as opposed to from ‘all of time’). This is probably perfect for what you’re doing, you’ve totally got this part right: where to_date(‘yyyyMMdd’,my_dt) >= addday(current_date(),-5) After you’ve implemented these tweaks, I would try to run your query with CONTEXT (‘QUERYTIMEOUT’ = 0), to give your query as much time as it needs to run, and to generate an execution plan. Hope this helps!
Denodo Team
24-08-2018 10:49:12 -0400
You must sign in to add an answer. If you do not have an account, you can register here