Better codes to develop

DIFFERENCES

  select CE1.CLASS_ID, 
         CE1.LOCATION_ID,
         count(case CE1.FORMAT_ID  when 5 then 1 end) as LIVE,
         count(case CE1.FORMAT_ID  when 14 then 1 end) as LB,
         count(case CE1.FORMAT_ID  when 15 then 1 end) as WEB,
         SUM(DECODE (CE1.ROLE_ID,5,1,0)) FACULT开发者_JS百科Y,
         SUM(DECODE  (CE1.ROLE_ID ,2,1,0)) MODERATOR,
         SUM(DECODE  (CE1.ROLE_ID ,3,1,0)) PANELIST,  
         SUM(DECODE  (CE1.ROLE_ID,4,1,0)) PRESENTER,
         COUNT(CE1.USER_ID) TOT 
    from C_EDUCATION1 CE1 
GROUP BY CE1.LOCATION_ID, CE1.CLASS_ID

We are developing existing system and we found mixed use COUNT and DECODE. If we understand correctly, we could convert to following codes.

  select CE1.CLASS_ID,  
         CE1.LOCATION_ID, 
         SUM(DECODE (CE1.FORMAT_ID ,5,1,0)) LIVE,
         SUM(DECODE  (CE1.FORMAT_ID ,14,1,0)) LB, 
         SUM(DECODE  (CE1.FORMAT_ID,15,1,0)) WEB,
         SUM(DECODE (CE1.ROLE_ID,5,1,0)) FACULTY,
         SUM(DECODE  (CE1.ROLE_ID ,2,1,0)) MODERATOR,
         SUM(DECODE  (CE1.ROLE_ID ,3,1,0)) PANELIST,  
         SUM(DECODE  (CE1.ROLE_ID,4,1,0)) PRESENTER,
         COUNT(CE1.USER_ID) TOT 
    from C_EDUCATION1 CE1 
GROUP BY CE1.LOCATION_ID, CE1.CLASS_ID

OR All COUNT

We are wondering differences in performance, or others issues. Is it better way exist?

---

There will be no performance difference between the two.

Personally, I would tend to prefer a CASE-based solution. CASE is ANSI standard rather than an Oracle-specific function. Developers that are coming from other database platforms will probably have to look up the semantics of the DECODE statement. CASE is also relatively obvious to developers regardless of the language they're familiar with. Virtually every language has a CASE statement so any developer should be able to quickly see what you're doing.

That being said, I would tend to advocate something like

SELECT ce1.class_id,
       ce1.location_id,
       SUM( CASE WHEN format_pkg.is_live( format_id ) = 'Y' THEN 1 ELSE 0 END ) live,
       SUM( CASE WHEN format_pkg.is_lb(   format_id ) = 'Y' THEN 1 ELSE 0 END ) lb,
       ...

This uses the CASE from the first query, the SUM from the second, and adds some function calls to determine what type of format/ role is being discussed. This prevents you from having to hard-code the FORMAT_ID/ ROLE_ID values all over the place. And using SUM rather than COUNT is a bit clearer since it's probably not immediately obvious to a developer that COUNT excludes the NULL values.