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.

SELECT DISTINCT c.my_id
      ,c.my_name
      ,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

vs

SELECT my_id
      ,my_name
      ,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...

SIGNs of Friday

I just thought I'd share this thought that came to mind when looking for a solution to a simple problem.

I learnt DECODE before I encountered CASE, so often my brain thinks that way first.

Therefore, I came up with an expression that uses SIGN, which I'm sure you don't see often.

SELECT SUBSTR(product_description,1,40)
    ||DECODE(SIGN(LENGTH(product_description)-40),1,'...') product_description
   -- CASE WHEN LENGTH(product_description) > 40 THEN '...' END product_description
FROM oe.product_information;

The case equivalent is commented.

This place ellipses at the end of truncated strings longer than 40 characters.