 General Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums General 15 Jul 2014 Can we use coalesce and aggregate function in one derived columns Hi All,   My scenario here is I need to apply some logic to a table. consider there are 4 table a,b,c and we have 5 columns in each like a1,a2,a3,a4,a5 similarly for table b (b1,b2,b3,b4,b5,b6) and table c(c1,c2,c3,c4,c5). I need to apply the below logic to get the appropriate value: INSERT INTO sample( c1, c2, c3, b4, coalesce(main.a5,0) + SUM(CASE WHEN b.b6 = '+' THEN COALESCE(b.b5,0) WHEN b.b6 = '-' THEN -1*COALESCE(b.b5,0) ELSE 0 END) AS data from b left join c on (b.b3 = c.c3) left join a on (c.c3 = a.a3) group by 1,2,3,4,5;   When i execute the same i get error on group by saying 'group by clause may not contain aggregate column(sum)' If i remove group by like  INSERT INTO sample( c1, c2, c3, b4, coalesce(main.a5,0) + SUM(CASE WHEN b.b6 = '+' THEN COALESCE(b.b5,0) WHEN b.b6 = '-' THEN -1*COALESCE(b.b5,0) ELSE 0 END) AS data from b left join c on (b.b3 = c.c3) left join a on (c.c3 = a.a3) group by 1,2,3,4;   This also gives error like 'non aggregated column (coalesce) must be a part of group by clause.   Since both the things happens in same column its contrary. I know this wont work. Can any one suggest a better way for this...      15 Jul 2014 I can't follow your logic, so I don't know if this is correct, but adding an aggregate function to the coalesce(main.a5,0) or removing the SUM should at least remove that error: INSERT INTO sample( c1, c2, c3, b4, coalesce(main.a5,0) + (CASE WHEN b.b6 = '+' THEN COALESCE(b.b5,0) WHEN b.b6 = '-' THEN -1*COALESCE(b.b5,0) ELSE 0 END) AS data from b left join c on (b.b3 = c.c3) left join a on (c.c3 = a.a3) group by 1,2,3,4,5; INSERT INTO sample( c1, c2, c3, b4, MIN(coalesce(main.a5,0)) + SUM(CASE WHEN b.b6 = '+' THEN COALESCE(b.b5,0) WHEN b.b6 = '-' THEN -1*COALESCE(b.b5,0) ELSE 0 END) AS data from b left join c on (b.b3 = c.c3) left join a on (c.c3 = a.a3) group by 1,2,3,4;   Dieter You must sign in to leave a comment.