Difference between Qualify and Having

Database
Fan

Difference between Qualify and Having

what is the difference between qualify and group in Teradata?


Accepted Solutions
Teradata Employee

Re: Difference between Qualify and Having

HAVING filters rows after GROUP BY, typically based on results of aggregate functions.

QUALIFY is similar, but is used to filter based on ordered analytical / windowed aggregate functions.

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Difference between Qualify and Having

HAVING filters rows after GROUP BY, typically based on results of aggregate functions.

QUALIFY is similar, but is used to filter based on ordered analytical / windowed aggregate functions.

sha
Fan

Re: Difference between Qualify and Having

Hi Fred,

 

I have a quesiton on Date field. I'm trying to add 1 year to an existing date field and I get ' invalid date' error all the time.
Using below query
select cast(EFFECTIVE_DT as date format 'yyyy-mm-dd')+ interval '1' YEAR
Kindly help there!

Junior Contributor

Re: Difference between Qualify and Having

This is totally unrelated to the original question, better open a new thread.

 

Never use Intervals for adding months/years, you'll always end on a non-existing day like 2018-02-29 or 2018-06-31.

 

When add_months(EFFECTIVE_DT,12) is still failing you got a date close to the max, probably 9999-12-31, then you need to apply a CASE.

Junior Supporter

Re: Difference between Qualify and Having

Hi,

Does your EFFECTIVE_DT has date like '2016-02-29' ? If you add 1 year using interval, it will lead to '2017-02-29', which is wrong as 2017 is not a leap year. Interval doesnt have date intelligence, no better not use it. Its better to use ADD_MONTHS function here. Please use

select ADD_MONTHS (EFFECTIVE_DT , 12), this would work.

--Samir

Fan

Re: Difference between Qualify and Having

Thanks you Fred