Problems with Select Case and Group By 1

[goranm]

Hello!

My question is if it is possible to have a "Select Case" with a SUM (or MAX) and the have a GROUP BY.

We have a alphanumeric field where there in some specified positions will be values for length, width and heigth.

I have to pick out the biggest value, but I also want to check and see if it´s a numeric value that is stored in the field.

But it´s seems to be a problem when I try to use it with a group by function.
Is it possible to do it, and how shall I then change my query?

My query looks just now like this:

SELECT
  COID,
  COPOS
  MAX(TRIM(SUBSTR(ROW.INSTRUCTIONS,112,9))) LENGTH,
  CASE
     WHEN LENGTH(TRIM(TRANSLATE (TRIM(SUBSTR(ROW.INSTRUCTIONS,130,9)), ' +-.0123456789',' '))) IS NULL THEN
        SUM(TRIM(SUBSTR(ROW.INSTRUCTIONS,130,9))) 
     ELSE
        SUM(0)
  END AS HEIGHT,
  SUM(TRIM(SUBSTR(ROW.INSTRUCTIONS,176,16))) WEIGHT
FROM
  ROW
GROUP BY
  COID,
  COPOS

Thanks in advance
/Goran

 

[hoinz]

I suppose you should reverse CASE and SUM.
Untested, but something like:

SUM(
CASE
     WHEN LENGTH(.....)) IS NULL THEN
        TRIM(....))
     ELSE
        0 )
  END AS HEIGHT

hope this helps

 

[hoinz]

oh, bracket in the wrong place, sorry:
ELSE
0
END ) AS HEIGHT

 

[jimirvine]

Add the instructions column into the group by

 

[goranm]

Thank you hoinz. It worked perfectly.

/Goran