Failure 3504-non-aggregate values must be part of the associated group

General
Enthusiast

Failure 3504-non-aggregate values must be part of the associated group

Hi All,

 

I am facing error  :selected  non-aggregate values must be part of the associated group for below requirement please help me with the fix

 

Requirement=If the values we are trying to convert from seconds values to interval are more than 800000000 seconds, default it to max interval possible with Teradata (9999 23:59:59.000000)

 

SQL=

select case when MAX(col * INTERVAL '0000 00:00:01' DAY TO SECOND) > 800000000 * INTERVAL '0000 00:00:01' DAY TO SECOND THEN interval '9999 23:59:59.000000' day to second else col end as col from table

 

Note: col is decimal(19,4) and target is interval

 

Thanks,

Pavan

6 REPLIES 6
Teradata Employee

Re: Failure 3504-non-aggregate values must be part of the associated group

Hi Pavan,

 

You're having max(col * x) in the fisrt part of the case, but just col is the else part.

Try like this :

select case
         when max(col * interval '0000 00:00:01' day to second) > 800000000 * interval '0000 00:00:01' day to second
         then interval '9999 23:59:59.000000' day to second
         else max(col * interval '0000 00:00:01' day to second)
       end as col
  from table;
Enthusiast

Re: Failure 3504-non-aggregate values must be part of the associated group

Hi Walder,

My requirement is to fetch actual value if col * interval day to second > 800000000 is FALSE.

Thanks,
Pavan
Teradata Employee

Re: Failure 3504-non-aggregate values must be part of the associated group

Then remove the max, max is an aggregate function :

select case
         when col * interval '0000 00:00:01' day to second > 800000000 * interval '0000 00:00:01' day to second
         then interval '9999 23:59:59.000000' day to second
         else col * interval '0000 00:00:01' day to second
       end as col
  from table;
Teradata Employee

Re: Failure 3504-non-aggregate values must be part of the associated group

Why convert the values to INTERVALs before comparing?

select case
         when col > 800000000
         then interval '9999 23:59:59.000000' day to second
         else col * interval '0000 00:00:01' day to second
       end as col
  from table;
Enthusiast

Re: Failure 3504-non-aggregate values must be part of the associated group

if max(col) > 800000000 then default value else col value hope you got it please let me know if there is a way to achieve the goal

Thanks,
Pavan
Teradata Employee

Re: Failure 3504-non-aggregate values must be part of the associated group

MAX implies a group of rows, yet your CASE statement appears intended to compute values for individual rows. That's the confusion - it's still not clear what the goal is.

 

If the value in any row exceeds 800000000, then return the default for every row?

CASE WHEN (SELECT MAX(col) FROM table) > 800000000 THEN ...