Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: query advise/problem

    hie all,
    i have a query here and what im trying to do is simple...

    sum(everything with the origin_id = CLP and bad) - sum(everything with the origin_id = VP and TAM and status = 10)...
    So the problem here is, Im not sure how to add the criteria status = 10 in the second summation...

    I tried in many ways but it doesnt return the correct value...
    anyone could help me here ?


    Code:
    SELECT SUM(DECODE(ORIGIN_ID,'CLP',RELOADS_VALUE_J2,DECODE(ORIGIN_ID,'bad',RELOADS_VALUE_J2))) - SUM(DECODE(ORIGIN_ID,'VP',RELOADS_VALUE_J2,DECODE(ORIGIN_ID,'TAM',RELOADS_VALUE_J2))) AS SUM_PPB
    FROM TRAP_RA_PPB_BALANCE

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Instead of using decode use 'case when', see the sql manual for more info.

    Alan

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You dont need that many decodes, something like this..

    sum( decode( origin_id, 'CLP', reloads_value_j2, 'bad', reloads_value_j2 ) ) - sum( decode( origin_id, 'VP', reloads_value_j2, 'TAM', reloads_value_j2, 0 ) + decode( status, 10, reloads_value_j2, 0 ) )

    should do. However, I agree with AlanP on using CASE (more clear).

  4. #4
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    Jmartinez,
    that code u gave doesnt really returns what i want, I was saying that the status = 10 should be in the 2nd summation... in which origin_id VP and TAM and status = 10...

    anyway ive tried using case when but im stuck

    Code:
    select
       case 
     	 when origin_id = 'CLP' then sum(reloads_value_j2) 
    	 when origin_id = 'bad' then sum(reloads_value_j2)
    	 end
    from trap_ra_ppb_balance
    ????

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You put the case inside the sum() just like your decode. The reason you use case is because you can use ANY expression (as you would use in the where clause so you can use LIKE, IN etc) you want unlike decode which is restricted to values.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •