Re: Sum on like wildcard - replacing decode statement

On 8 Jun 2005 12:47:51 -0700, bbcrock_at_gmail.com wrote:

>I need to replace a decode statement with some other process.
>
>currently the old DBA's code reads:
>sum(decode(status 'PENDING', 1, 0)) PENDING
>
>We added several new "PENDING" entries. I need to rewrite it as:
>sum(decode(status 'PENDING%', 1, 0)) PENDING
>
>decode fails here. Does anyone have a working example of a case
>statement or similar code?

 The CASE statement is more flexible, and it's also more "standard".

sum(
 case
  when status like 'PENDING%' then 1
  else 0
 end
) PENDING

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool