locked
reuse select query result RRS feed

  • Question

  • User-254703530 posted

    Hi,

    I have a SQL select query, it works fine as it is now.

    select namee as 'FULL NAME', 
    sum(inn)  as 'Money', sum(case when color='red' or color='MEDIUM red' or color = 'LIGHT red' then isnull(out,0) else 0       End) as '[B]FIRST RESULT[/B]',
    sum(case when color='red' or color='MEDIUM LIGHT red' then isnull(inn,0) - isnull(out,0) else 0       End) as '[B]SECOND RESULT'[/B]
    from yyyyyyyy where  ID = 100 group by namee

    But I would like to be able to get the result of each case ex: final result of "[B]FIRST RESULT[/B]" and "[B]SECOND RESULT[/B]" and to be able to reuse it in my next line of code like

    sum(case when color='red' or color='MEDIUM LIGHT red' then isnull(inn,0) - isnull([B]'FIRST RESULT'[/B],0) else 0       End) as '[B]THIRD RESULT'[/B]

    I'm not sure how i can accomplish that, any tip will be appreciated.

    Thanks




    Tuesday, October 9, 2012 12:23 PM

Answers

All replies

  • User269602965 posted

    nested query

    select
      a.FULL_NAME,
      a.MONEY,
      a.FIRST_RESULT,
      a.SECOND_RESULT,
      sum(
         case 
           when color='red' or color='MEDIUM LIGHT red' then isnull(inn,0) - isnull(a.FIRST_RESULT,0) 
           else 0
          end  
         ) AS THIRD_RESULT    
    from
      (
      select 
        namee as 'FULL_NAME', 
        sum(inn)  as 'Money', 
        sum(
           case 
             when color='red' or color='MEDIUM red' or color = 'LIGHT red' then isnull(out,0) 
             else 0       
           End
           ) as FIRST_RESULT,
        sum(
           case 
             when color='red' or color='MEDIUM LIGHT red' then isnull(inn,0) - isnull(out,0) 
             else 0       
           End
           ) as SECOND_RESULT 
      from 
        yyyyyyyy 
      where  
        ID = 100 
      group by namee
      ) a
    group by
      a.FULL_NAME,
      a.MONEY,
      a.FIRST_RESULT,
      a.SECOND_RESULT;

    Tuesday, October 9, 2012 6:45 PM
  • User-254703530 posted

    Hi,

    I didn't try your code, but i don't think it will work the way i want it.

    basically this what i want to do

    select namee as 'FULL NAME',
    sum(inn)  as 'Money', 
    sum(case when color='red' or color='MEDIUM red' or color = 'LIGHT red' then isnull(out,0) else 0       End) as 'FIRST RESULT',
    sum(case when color='red' or color='MEDIUM LIGHT red' then isnull(inn,0) - isnull(out,0) else 0       End) as 'SECOND RESULT'
    sum(case when color='red' or color='MEDIUM LIGHT red' then isnull(inn,0) - isnull('FIRST RESULT',0) else 0       End) as 'THIRD RESULT'
    from yyyyyyyy where  ID = 100 group by namee

    To be able to use the reusult of the first sum calculation into my third sum calculation. "FIRST RESULT" sun in "THIRD RESULT" sum

    And i only showed only the first 3 sum calculation, in total i have more than 10, i'm not sure but i think if use ur method my query will be too long.

    thx



    Thursday, October 11, 2012 11:12 PM
  • User-1378764701 posted

    Hi,

    Use WITH statement for qry reuse.. please refer the link..

    http://www.dba-oracle.com/t_with_clause.htm

    Regards

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 13, 2012 11:37 AM