Re: [sqr-users] Summing/Sorting

[CGeorge@chubb.com]

George,

Thanks for your response. I had no idea that these functions existed.
However, I had no success in applying your sql to my own situation. Allow
me to be more specific.

I have a table, PS_PP_ITEM_TAO5, amongst whose columns are:-

ITEM
ITEM_LINE
PAY_AMT

I have 138 rows

Aggregating the PAY_AMT at the ITEM level leaves me some 45 rows. Since I
have the data in front of me I can say which rows I need to sum to my total
of 142010.52. How can I represent this as a query using the analytical
function syntax?

Rgds.,

CG


                                                                           
             "George Jansen"                                               
             <GJANSEN@aflcio.o                                             
             rg>                                                        To 
             Sent by:                  <sqr-users@sqrug.org>               
             sqr-users-bounces                                          cc 
             +cgeorge=chubb.co                                             
             m@sqrug.org                                           Subject 
                                       Re: [sqr-users] Summing/Sorting     
                                                                           
             08/06/2005 13:56                                              
                                                                           
                                                                           
             Please respond to                                             
             "This list is for                                             
             discussion about                                              
             the SQR database                                              
                 reporting                                                 
               language from                                               
                 Hyperion                                                  
                Solutions."                                                
             <sqr-users@sqrug.                                             
                   org>                                                    
                                                                           
                                                                           




If you are using Oracle 8i and above, you can do this in SQL with the
"analytic functions":

SQL> select object_id
  2  from (select object_id, sum(object_id) over (order by object_id) as
osum
  3        from user_objects)
  4  where osum < 150000;

 OBJECT_ID
----------
     23133
     23134
     23135
     23136
     23137


>>> CGeorge@chubb.com 06/08/05 5:15 AM >>>
Forgive me for not scanning the archives for a solution to this problem,
but I'm not really sure of a succint enough search term.

However, I feel that this is a problem that must have been explored by
someone at some time.

I'm looking for a routine that will, from a list of numbers, sum together
combinations of those numbers until a user defined total is reached and
then present those numbers that have been selected to sum to that total.

Any pointers greatfuly received.

Rgds.,

CG
---

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


---

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users