Re: Challenging SQL Query Problem. Can you solve it?

On 22 Dec 2005 13:45:39 -0800, "Karen Hill" <karen_hill22_at_yahoo.com> wrote:

>T3 wrote:
>> After looking at both your queries I think it's best if you just
>> combine them into one that sums both sold and lost for each
>> salesperson. I think this will work for you.
>>
>> SELECT leads.salesman AS Salesperson,
>> COUNT(DECODE(LOST,-1,1,0)) AS [Number of Lost],
[snip]
>
>Thank you for your response. Is there any way to do it without the
>DECODE? My DBMS doesn't have DECODE unfortunately.

 The more portable replacement for Oracle's DECODE is the CASE statement.

 DECODE(x, 1, 'a', 2, 'b', 'c')

 =>

 CASE
  WHEN x = 1 THEN 'a'
  WHEN x = 2 THEN 'b'
  ELSE 'c'
 END  or

 CASE x
  WHEN 1 THEN 'a'
  WHEN 2 THEN 'b'
  ELSE 'c'
 END

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool