3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Database
N/A

3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Hi, I need to be able to select the max value in a column when a certain condition is true. After that, i would need to append either an 'X' or a depending on the same condition plus another condition. Here is what I got initially:
case when (substr(a.field1,1,4)) = (current_date (format 'YYYY'))
then
max(a.field2)
end

That's as far as I got since I already get the error:
3504: Selected non-aggregate values must be part of the associated group.

Thing is after that I would need to append either an 'X' or a to max(a.field2) if the ff: condition is true:
(substr(a.field1,1,4)) = (current_date (format 'YYYY')) and ((substr(c.field1,1,5)) = '12/30')

I would need to select multiple columns for this, like the next condition for selecting max(a.field2) would be when (substr(a.field1,1,4)) = (current_date -1 (format 'YYYY')).

I'm new to teradata so this is pretty rocket science to me.. :-s

Can anyone give me some thoughts on this?

Thanks!
15 REPLIES

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Hi,
with certain level of simplification: what is in the select part must by in a group by clause or inside of some aggregation function. Applied to the first example:
GROUP BY SUBSTR(a.field1,1,4)
Keep in mind, that column will contain a NULL values for the rows unsatisfying the condition (like empty cell in Excel).
May be, if you explain in “business language” what is the goal of the query, the answer would be more pointed.

- Petr

Teradata Employee

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

You can't put an aggregate inside a CASE, but you can put a CASE inside an aggregate:

MAX(case
when substr(a.field1,1,4) = (CAST(current_date as format 'YYYY') as CHAR(4))
then a.field2
else NULL
end)

Does that help?
N/A

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Thanks for the responses Petr and Fred Pluebell! You're right, I think I did not express myself correctly in the previous post, I might have even misconstrued the condition - my apologies.

Petr:

The goal of the query is to come up with a table. I need to select the max value of a certain column (a.field2) for a specific ID that matches the month that I need.

For example, a specific ID can have 100 records, but narrowing down the selection to the month I need -with the condition substr(a.field1,1,4)) = (current_date (format 'YYYY')) would only give me 12 records. a.field1 is a date with the format YYYYMM. From those 12 records, I would need the row with the max value for field2.

Upon getting that max value, I would then need to append either an 'X' or a blank space ' ' after the obtained value depending on the MONTH AND DATE value. This month and date value on the other hand is obtained from another table - b, where the date value should be before '12/30' to get the X, otherwise it gets the blank space.

Fred Pluebell, I tried your suggestion and I got farther than before! :) However, does that only work when selecting that one column? Because when I tried adding the selection of two other columns before selecting the maximum value (or even when trying it after the max value), I get the 3504 error again:

select

a.field1,

current_date (format 'YYYY'),

(MAX (CASE

when substr(a.field1,1,4) = (CAST (extract (year from current_date) as char(4)))

then

cast (a.field2 as CHAR (5))

else 0

end))

Additional question, on the "then" clause in the case above, can I add the append (||) function? I'm thinking of when I'll be adding the condition for appending the 'X' or blank space...
N/A

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

I have tried using max and somehow it returns only the first character of the maximum value instead of the whole thing. any idea why?

MAX (CASE when substr(a.field1,1,4) = (CAST (extract (year from current_date) + 1 as char(4)))
then
cast (a.field2as CHAR format 'X(5)')
end ) as MAX_field2

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Karver,
Please dont'd take it amiss, I write it quite stright. It's bit difficult to jump directly in writting a query without SQL fundamental regardless of plaform.

Suppose there are two tables A and B. To join them, you need to proper join condition, the most often using Primary key (unique value) in one table and Foreign key (usualy non-unique) in the second one.

Let A has columns:
- pk - some primary key of table A
- field1 CHAR(6) - containing year and month part of date in format YYYYMM, if there should be whole date, then field must be either in different format like YYMMDD or different data type (INTEGER, DATE, CHAR(8))
- fk - some field linked to the table B

and B has columns:
- pk - some primary key of table B
- field1 CHAR(10) - containing date in format 'MM/DD/YYYY'

SELECT MAX(A.field2 || CASE WHEN SUBSTR(B.field1,1,5) < '12/30' THEN 'X' ELESE ' ' END) AS required_output
FROM
A
INNER JOIN
B
ON A.fk = B.pk /* depends on data structure */
WHERE SUBSTR(A.field1,1,4)) = current_date (format 'YYYY')
;

This query should be syntactically correct, but still I'm not sure, whether it is, what you want. Notice that condition substr(a.field1,1,4)) = (current_date (format 'YYYY')) is true for rows, where substr(a.field1,1,4) = '2009', i.e. current year.

Regarding second query you tried - see comments above: it should be SELECT A.field1, Max(what ever) FROM A GROUP BY A.field1;

- Petr
N/A

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Thanks Petr, I do appreciate your input. I'm a newbie at sql so I need all the help i can get:)

Thing is, im going to have multiply columns with teh field 2 value. only the difference is the condition will increment, i.e., first time i select it, a.field1 is compared to current year, the second column to get the max value will compare a.field1 to current year +1 and so on (until 5 columns).

Here's what i got so far (i used the lpad since our requirements is to right justify the field):

SELECT

cast (CURRENT_DATE as date format 'MMDDYY') || ',' (CHAR(7)) SYSDATE,

lpad((cast (B.box as char)) ,6, ' ') || ',' (CHAR(7))BOX ,

'"' || lpad((cast(CASE WHEN B.EXP_DATE < A.END_DATE

THEN A.MAX_FIELD || 'X'

ELSE A.MAX_FIELD || ' '

END as char)), 6, ' ') || '"' || ',' (CHAR(9)) Y1,

'"' || lpad((cast(CASE WHEN B.EXP_DATE < A.END_DATE1

THEN A.MAX_FIELD1 || 'X'

ELSE A.MAX_FIELD1 || ' '

END as char)), 6, ' ') || '"' || ',' (CHAR(9)) Y2,

'"' || lpad((cast(CASE WHEN B.EXP_DATE < A.END_DATE2

THEN A.MAX_FIELD2 || 'X'

ELSE A.MAX_FIELD2 || ' '

END as char)), 6, ' ') || '"' || ',' (CHAR(9)) Y3,

'"' || lpad((cast(CASE WHEN B.EXP_DATE < A.END_DATE3

THEN A.MAX_FIELD3 || 'X'

ELSE A.MAX_FIELD3 || ' '

END as char)), 6, ' ') || '"' || ',' (CHAR(9)) Y4,

'"' || lpad((cast(CASE WHEN B.EXP_DATE < A.END_DATE4

THEN A.MAX_FIELD4 || 'X'

ELSE A.MAX_FIELD4 || ' '

END as char)), 6, ' ') || '"' || ',' (CHAR(9)) Y5

FROM

(select

ID,

MAX (CASE when substr(tab.field1,1,4) = (CAST (extract (year from current_date) as char(4)))

then

cast (field2 as CHAR format 'X(5)')

end ) (CHAR(5)) AS MAX_FIELD,

MAX (CASE when substr(tab.field1,1,4) = (CAST (extract (year from current_date) + 1 as char(4)))

then

cast (field2 as CHAR format 'X(5)')

end ) as MAX_FIELD1,

(MAX (CASE when substr(tab.field1,1,4) = (CAST (extract (year from current_date) + 2 as char(4)))

then

cast (field2 as CHAR format 'X(5)')

end )) MAX_FIELD2,

(MAX (CASE when substr(tab.field1,1,4) = (CAST (extract (year from current_date) + 3 as char(4)))

then

cast (field2 as CHAR format 'X(5)')

end )) MAX_FIELD3,

(MAX (CASE when substr(tab.field1,1,4) = (CAST (extract (year from current_date) + 4 as char(4)))

then

cast (field2 as CHAR format 'X(5)')

end )) MAX_FIELD4,

CAST ('12/30/' || CAST (extract (year from current_date) as char(4)) AS DATE format 'MM/DD/YYYY') END_DATE,

CAST ('12/30/' || CAST (extract (year from current_date) + 1 as char(4)) AS DATE format 'MM/DD/YYYY') END_DATE1,

CAST ('12/30/' || CAST (extract (year from current_date) + 2 as char(4)) AS DATE format 'MM/DD/YYYY') END_DATE2,

CAST ('12/30/' || CAST (extract (year from current_date) + 3 as char(4)) AS DATE format 'MM/DD/YYYY') END_DATE3,

CAST ('12/30/' || CAST (extract (year from current_date) + 4 as char(4)) AS DATE format 'MM/DD/YYYY') END_DATE4

from table2 tab

group by ID) A

INNER JOIN table1 B

ON A.ID = B.ID

and A.MAX_FIELD is not null

and A.MAX_FIELD1 is not null

and A.MAX_FIELD2 is not null

and A.MAX_FIELD3 is not null

and A.MAX_FIELD4 is not null

What's wrong with what I did there is that, the max function selects only the first character or returns ? - instead of the whole value :(

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Hi,
see that you are only, I'll try to reply fast as leaveing the office today:
? stands for NULL and it is probably due to NULL has appered in some concateneated string use COALESCE(expre, '') to get an empty string instead of NULL. Regarding return just one char, try to cast the whole output columnt to CHAR (5) or bigger.
best regards
petr
N/A

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

For anyone who will encounter the same issue, this is working now. (the code also handles removing garbage characters when using fastexport)

The changes lie in these sections:
cast (MAX (CASE when substr(tab.field1,1,4) = (CAST (extract (year from current_date) as char(4)))
then
field2
end) as INTEGER) MAX_FIELD,

as well as in

lpad ((trim(A.MAX_FIELD (FORMAT 'ZZZZZ'))),5,' ') (CHAR(5)),
CASE WHEN B.EXP_DATE < A.END_DATE
THEN 'X'
ELSE ' '
END || '"' || ',' (CHAR(3)),
'"' (CHAR(1)),

Thanks for everyone who responded with their thoughts on this.
N/A

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Hi there...

I have been working on similar problem but my sql translation does not look god for now.

Sorry for terrible question bot for now I have no idea how to find good solution.

select event, logonsource, username, max(logontime) from log_TABLE group by username, event

I have the same communicate like friend from first post.

Select failes 353: Selected non-aggregate values must be part of the associated group.

Can I youse som of your help?