locked
Case Statement help RRS feed

  • Question

  •  select case when balcode <> 'ACTUALS' then full_value else 0 end / case when balcode <> 'Q' then 10 else 1 end 
     
      
                    from oas_balance                  
    where   (yr = 2011 and (period <= 1))
             
                   and  balcode = 'actuals' and  repbasis = 0 
     select balcode,full_value
     
    With the above code i am trying to figure out the QTy ... when i run the statement i get all the values in my result set as 0... can someone help me ?

    FM

    Thursday, November 29, 2012 5:36 PM

Answers

  • Well, since you're running with BalCode = 'actuals' and in this case you use 0 (case when balcode <> 'Actuals' then full_value else 0 end in your case statement, the quantity will always be 0.

    What exactly you're trying to achieve?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, November 29, 2012 5:41 PM
    • Marked as answer by Farhan1 Friday, November 30, 2012 2:33 PM
    Thursday, November 29, 2012 5:41 PM
  • If you will run your report with T3.Balcode = 'Actuals', you still going to get 0.

    Your code re-write was OK, but the condition is supposed to produce 0 for Actuals.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, November 29, 2012 5:51 PM

All replies

  • Can you please share some data for this query.

    Thursday, November 29, 2012 5:39 PM
  • Well, since you're running with BalCode = 'actuals' and in this case you use 0 (case when balcode <> 'Actuals' then full_value else 0 end in your case statement, the quantity will always be 0.

    What exactly you're trying to achieve?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, November 29, 2012 5:41 PM
    • Marked as answer by Farhan1 Friday, November 30, 2012 2:33 PM
    Thursday, November 29, 2012 5:41 PM
  • sum(case  when T3."balcode" <> 'ACTUALS' 
       then (CAST(T3."full_value" AS FLOAT) / case  when (T3."balcode" <> 'Q') then 10 else 1 end ) * -1 else 0 end ) "c31"
    The above is the case statement from a  COGNOS report i am trying to convert it to SSRS... Basically above case will give me quantity...

    FM

    Thursday, November 29, 2012 5:47 PM
  • If you will run your report with T3.Balcode = 'Actuals', you still going to get 0.

    Your code re-write was OK, but the condition is supposed to produce 0 for Actuals.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, November 29, 2012 5:51 PM