Wednesday, 14 March 2012

A sign to refactor SQL

Note to fellow programmer: just wondering why the yearn for multiple subqueries when it seems a simple group by would suffice?

If you see multiple instances of the one table in a query, why not see if it can be re-factored.
Admittedly the query was originally querying from separate tables which got consolidated into a view, so the query pedigree was sharpened already - if that's the right term to use.

      ,TO_CHAR(c.my_date, 'DD/MM/YYYY HH:MI AM') my_date
      ,(SELECT SUM(cl.my_total)
        FROM my_view cl
        WHERE cl.my_id = c.my_id) my_value
      ,(SELECT COUNT(1)
        FROM my_view cl
        WHERE cl.my_id = c.my_id
        AND   cl.special_nbr_field > 0) my_cnt
FROM   my_view c


SELECT my_id
      ,TO_CHAR(my_date, 'DD/MM/YYYY HH:MI AM') my_date
      ,SUM(my_total) my_value
      ,SUM(SIGN(special_nbr_field)) item_cnt
from my_view
group by my_id, my_date, my_name

Don't feel negative - I added an elegant use of SIGN

I fear my puns are getting worse...

No comments: