All Forums Database
bhedrick 10 posts Joined 04/16
05 Apr 2016
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!

dnoeth 4628 posts Joined 11/04
05 Apr 2016

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.

Dieter

AtardecerR0j0 71 posts Joined 09/12
06 Apr 2016

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.

Be More!!

yuvaevergreen 93 posts Joined 07/09
06 Apr 2016

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')

 

 

bhedrick 10 posts Joined 04/16
06 Apr 2016

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.  

ToddAWalter 316 posts Joined 10/11
06 Apr 2016

What data type is date_chk?

dnoeth 4628 posts Joined 11/04
06 Apr 2016

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

Dieter

bhedrick 10 posts Joined 04/16
06 Apr 2016

Integer.

dnoeth 4628 posts Joined 11/04
06 Apr 2016

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 :)
 
 

Dieter

ToddAWalter 316 posts Joined 10/11
06 Apr 2016

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.

bhedrick 10 posts Joined 04/16
06 Apr 2016

Thank you so much!  That works and I really appreciate the explanation of why the ADD_MONTHS didn't work as I had it.

You must sign in to leave a comment.