|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here! |
|
#1
|
|||
|
|||
|
Problems with case statement in group by clause
I am having problems using select case to selectively choose a value that will be used in a group-by clause. The SQL works without the case. I am able to use a case in a select alone, without a group-by. This is a UDB database.
Been banging my head on this for a few days, any help would be greatly appreciated: The following SQL returns the error: "SQL0206N "ALLOC_PCT" is not valid in the context where it is used. SQLSTATE=42703" SELECT DBBLTRN.FITBT021_BLTO_ENT.BILL_ENTTY_C, DBBLTRN.FITBT231_BR_HRDWR.RPT_Y_MO, DBBLTRN.FITBT121_BLTO_SBU.BILL_TO_ENTTY_CHRG_PT, DBBLTRN.FITBT231_BR_HRDWR.CHRG_TY_C, Count(DBBLTRN.FITBT231_BR_HRDWR.CMPTR_NA) AS Count, case when DBBLTRN.FITBT121_BLTO_SBU.BILL_ENTTY_C is null then 1 else DBBLTRN.FITBT121_BLTO_SBU.BILL_TO_ENTTY_CHRG_PT end AS Alloc_Pct FROM ((DBBLTRN.FITBT021_BLTO_ENT RIGHT JOIN DBBLTRN.FITBT231_BR_HRDWR ON DBBLTRN.FITBT021_BLTO_ENT.BILL_ENTTY_C = DBBLTRN.FITBT231_BR_HRDWR.BILL_ENTTY_C) LEFT JOIN DBBLTRN.FITBT121_BLTO_SBU ON DBBLTRN.FITBT231_BR_HRDWR.BILL_ENTTY_C = DBBLTRN.FITBT121_BLTO_SBU.BILL_ENTTY_C) LEFT JOIN DBBLTRN.FITBT171_CHGTYINP ON (DBBLTRN.FITBT231_BR_HRDWR.RPT_Y_MO = DBBLTRN.FITBT171_CHGTYINP.RPT_Y_MO) AND (DBBLTRN.FITBT231_BR_HRDWR.CHRG_TY_C = DBBLTRN.FITBT171_CHGTYINP.CHRG_TY_C) GROUP BY DBBLTRN.FITBT021_BLTO_ENT.BILL_ENTTY_C, DBBLTRN.FITBT231_BR_HRDWR.RPT_Y_MO, DBBLTRN.FITBT121_BLTO_SBU.BILL_TO_ENTTY_CHRG_PT, DBBLTRN.FITBT231_BR_HRDWR.CHRG_TY_C, Alloc_Pct --------------------------------------------------------- This SQL works: SELECT DBBLTRN.FITBT021_BLTO_ENT.BILL_ENTTY_C, DBBLTRN.FITBT231_BR_HRDWR.RPT_Y_MO, DBBLTRN.FITBT121_BLTO_SBU.BILL_TO_ENTTY_CHRG_PT, DBBLTRN.FITBT231_BR_HRDWR.CHRG_TY_C, Count(DBBLTRN.FITBT231_BR_HRDWR.CMPTR_NA) AS Count, DBBLTRN.FITBT121_BLTO_SBU.BILL_TO_ENTTY_CHRG_PT FROM ((DBBLTRN.FITBT021_BLTO_ENT RIGHT JOIN DBBLTRN.FITBT231_BR_HRDWR ON DBBLTRN.FITBT021_BLTO_ENT.BILL_ENTTY_C = DBBLTRN.FITBT231_BR_HRDWR.BILL_ENTTY_C) LEFT JOIN DBBLTRN.FITBT121_BLTO_SBU ON DBBLTRN.FITBT231_BR_HRDWR.BILL_ENTTY_C = DBBLTRN.FITBT121_BLTO_SBU.BILL_ENTTY_C) LEFT JOIN DBBLTRN.FITBT171_CHGTYINP ON (DBBLTRN.FITBT231_BR_HRDWR.RPT_Y_MO = DBBLTRN.FITBT171_CHGTYINP.RPT_Y_MO) AND (DBBLTRN.FITBT231_BR_HRDWR.CHRG_TY_C = DBBLTRN.FITBT171_CHGTYINP.CHRG_TY_C) GROUP BY DBBLTRN.FITBT021_BLTO_ENT.BILL_ENTTY_C, DBBLTRN.FITBT231_BR_HRDWR.RPT_Y_MO, DBBLTRN.FITBT121_BLTO_SBU.BILL_TO_ENTTY_CHRG_PT, DBBLTRN.FITBT231_BR_HRDWR.CHRG_TY_C, DBBLTRN.FITBT121_BLTO_SBU.BILL_TO_ENTTY_CHRG_PT |
![]() |
| Viewing: Dev Shed Forums > Databases > DB2 Development > Problems with case statement in group by clause |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|