A Gimmick with Group Functions

David Clement

September 2004

Oracle versions 7-8

The SQL group functions nearly always return some value, usually zero, if no row is found. This provides a useful coding gimmick for replacing two SQL statements with one. Here is the original code.

          exec sql select to_char (acctstartdt, 'DDMONYYYY')
          into :chToday
          from crd_rqt_accts
          where acctid = :unAcctId;
          
          if (strlen (chToday) == 0)
              exec sql select to_char (sysdate, 'DDMONYYYY') 
              into :chToday
              from dual;

But a single statement that uses a group function can do the same job as follows.


						            exec sql select to_char (decode (count (*), 
          0, sysdate, 
          max (acctstartdt)), 'DDMONYYYY')
          into :chToday
          from crd_rqt_accts
          where acctid = :unAcctId;

There is no practical difference in the execution plans of the first two SQL statements. The optimizer does invoke a sort for the group functions, but since it receives exactly one row to sort, it takes no measurable time. The only effect is to eliminate the overhead of the second SQL statement.