Filtering on dates - Explain plan looks right but it returns 0 rows.

Database
Enthusiast

Filtering on dates - Explain plan looks right but it returns 0 rows.

I'm trying to set up a report that will run monthly and return data from the prior month.  I found a lot of information about how to do this but the only way I can get any data returned is to manually enter the dates.  I'm new to Teradata.  I appreciate your help!  

The commented out section is one of the other things I tried.

SELECT ADD_MONTHS((DATE - EXTRACT(DAY FROM DATE)+1), -1) report_date, b.col1, 
SUM(a.col2) in_adh,
SUM (a.col3) out_adh
FROM view.dim_a_a a
JOIN view.b_b b
ON a.vw_ck=b.vw_ck
WHERE LOWER(a.totalrow)<>'total'
AND a.date_ck
BETWEEN ADD_MONTHS (CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1), -1) AND CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)
/* ADD_MONTHS(CURRENT_DATE-1 -EXTRACT(DAY FROM CURRENT_DATE-1) +1 ,-1)
AND CURRENT_DATE-1 -EXTRACT(DAY FROM CURRENT_DATE-1)*/

GROUP BY 1, b.col1
ORDER BY b.col1

The explain plan shows the date filter is correct.  If I copy it from the explain plan and run it, the first part returns dates but the second part doesn't return any rows.  

          (view_tables.a_a IN VIEW

          view.a_a.date_ck >= 1160301) AND

          ((view_tables.a_a in view

          view.a_a.date_ck <= 1160331) 

Thanks!

10 REPLIES
Senior Apprentice

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

Unless you run it on the first day of a month both BETWEEN should return the same result.

Did you double check if both Explains are the same?

What's your TD release, 1160301 instead of DATE '2016-03-01' looks strange.

Teradata Employee

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

This is the first part of your filter:

 (view_tables.a_a IN VIEW view.a_a.date_ck >= 1160301)->returns dates

and this is the second one:

(view_tables.a_a in view view.a_a.date_ck <= 1160331)->doesn't return any rows

Then the dates returned in the first filter must be greater than 31 march 2016 and there's no rows in march 2016.

So I think this must be a data problem no a technical problem.

Enthusiast

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

Its working for me....

SEL ADD_MONTHS (date '2014-04-01' - (EXTRACT(DAY FROM DATE '2014-04-01') - 1), -1),

date '2014-04-01' - EXTRACT(DAY FROM date '2014-04-01')

Enthusiast

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

Thanks for the responses.  The TD Version is 14.10.0.04 and database version is 14.10.06.07.

Dieter, I don't know what you mean by both Explains.  I hit f6 and it shows me one answerset.  

AtardecerR0j0 and yuvaevergreen - When I try to replace current_date with actual dates in the query, I get 0 rows returned.

If I run the query like this: (a.date_ck between '20160301' and '20160331'), I get all the data from the prior month.  

Teradata Employee

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

What data type is date_chk?

Senior Apprentice

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

Todd is probably right, what's the data type of date_chk: DATE, INTEGER, CHAR?

Enthusiast

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

Integer.

Senior Apprentice

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

One expects a DATE when a column is named DATE, so it's important infomation when it's an INT instead.

Currently you're comparing DATEs and INTEGERs and they simply don't match.

In Explan you could see that 2016-01-31 is 1160301 as INT and not 20160331.

Teradata stores DATEs using following formula:

(year - 1900) * 1000
+ month * 100
+ day

So you must add some additional calculations:

a.date_ck
BETWEEN CAST(ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1), -1) AS INT) + 19000000
AND CAST(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) AS INT) + 19000000

That's the penalty when a date is not stored as a date, you can't use existing date calculations :)

Teradata Employee

Re: Filtering on dates - Explain plan looks right but it returns 0 rows.

If "(a.date_ck between '20160301' and '20160331')" works as written and date_chk is integer, then the dates are not Teradata date storage and the 1160331 form will not compare correctly. You will have to do integer arithmetic to create the integer 20160301. Also, comparing to character strings adds overhead and possibly removes good query plans when comparing to integers.