problems grouping with a case statement

drsmyth · #1 (**permalink**) 08-12-08, 06:22

Hi everyone, this is my first post so go easy on me!!
I'm relatively new to the DB2 world and am having a bit of an SQL problem.

Basically, i'm struggling to goup by the result of a case statement..

Here's the case statement:

Code:

(CASE WHEN PROD.PROB_DFLT_PCT_RES IS NOT NULL THEN PROD.PROB_DFLT_PCT_RES * 100
WHEN PROD.PRBL_DFLT_PCT IS NOT NULL THEN PROD.PRBL_DFLT_PCT
ELSE IP.PROB_DFLT_PCT END) AS PD_PCT

this gives me a number that's actually a dimension, when i include it in my group by i get an error:

Code:

SQL0206N  "PD_PCT" is not valid in the context where it is used

Can anybody help me find a way around this?

n_i · #2 (**permalink**) 08-12-08, 08:08

Quote:

Originally Posted by drsmyth

Can anybody help me find a way around this?

You cannot use the new column alias (PD_PCT) in the same subselect where it is defined; put the entire case statement in the GROUP BY clause.

Peter.Vanroose · #3 (**permalink**) 08-12-08, 17:55

Or place the CASE expression in a nested select:

Code:

SELECT PD_PCT, COUNT(*), ...
FROM (SELECT ..., 
             CASE WHEN PROD.PROB_DFLT_PCT_RES IS NOT NULL
                  THEN PROD.PROB_DFLT_PCT_RES * 100
                  WHEN PROD.PRBL_DFLT_PCT IS NOT NULL
                  THEN PROD.PRBL_DFLT_PCT
                  ELSE IP.PROB_DFLT_PCT END AS PD_PCT
      FROM PROD INNER JOIN IP ON ...) X
GROUP BY X.PD_PCT

Peter.Vanroose · #4 (**permalink**) 08-12-08, 17:58

Or even slightly shorter (and more readable?):

Code:

SELECT PD_PCT, COUNT(*), ...
FROM (SELECT ..., 
             COALESCE(PROD.PROB_DFLT_PCT_RES * 100,
                      PROD.PRBL_DFLT_PCT,
                      IP.PROB_DFLT_PCT)     AS PD_PCT
      FROM PROD INNER JOIN IP ON ...) X
GROUP BY X.PD_PCT

Internet Services

Web Development

Digital Marketing

Consumer Tech