RE: [sqr-users] Need Some help in a SQR Select

["Kyle Leonard" <kyle.leonard@1answersolutions.com>]

I started this morning with the reply...I agree with everyone's response.
Here is a stab at the array notion of doing this:

LET #COUNTER = 0

BEGIN-SELECT
ITEM          &s_item
Balance amt   &n_balance_amt
BUSINESS UNIT &n_business_unit

        PUT &s_item &n_balance_amt &n_business_unit INTO ARRAY_ONE(#COUNTER)
        ADD 1 TO #COUNTER

END-SELECT

LET #X = 0
LET #Y = 0

WHILE #X <= #COUNTER
        LET #SUM = ITEM.ARRAY_ONE(#X)
                
        WHILE #Y <= #COUNTER
                IF #Y <> #X
                 LET #TEST_SUM = #SUM + ITEM.ARRAY_ONE(#Y)
                END-IF
                 IF #TEST_SUM = 140  !THIS COULD ALSO BE A LOOP IF NEEDED
                        DO SOME_PROCEDURE_TO_OUTPUT(#TEST_SUM)
                        BREAK
                 END-IF
                
                ADD 1 TO #Y
        END-WHILE

        ADD 1 TO #X
END-WHILE

I've done this rather quickly, so I may have missed a line or two of code.
I've seen this done on arrays with up to 40,000 'rows' without too much
trouble (depending on the server).

Kyle

_________________________________________________
Kyle J. Leonard, Consultant
kyle.leonard@1answersolutions.com
1Answer Solutions

-----Original Message-----
From: sqr-users-bounces+kyle.leonard=1answersolutions.com@sqrug.org
[mailto:sqr-users-bounces+kyle.leonard=1answersolutions.com@sqrug.org] On
Behalf Of Kaz.Narayanan@averydennison.com
Sent: Thursday, December 08, 2005 12:34 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Need Some help in a SQR Select

Hello All,

In my SQR, I am trying to select  a group of rows in PS  where the sum of
the amounts match some dolor amt.
Example.

In PS ITEM table we have following fields.
 ITEM
Balance amt
BUSINESS UNIT

let us say I have the following values in that table

      ITEM        Balance Amt       Business Unit
Row1  XYZ         100.00                  ABC
Row2     LMN            125.00                  KLM
Rwo2  KKK         40.00             PPP

in my SQR, I am trying to select row 1 and row 2 where the total of
Balance amt is 140.00 here is my procedure

Begin-Select
item

from ps_item HAVING sum(bal_amt) = 140
group by    ITEM

END-SELECT

I am not fetching any row, I am expecting the SQL to iterate with in all
rows where
it addes some rows to itself and sees where the total equals 140.00

any suggestions would be highly appreciated.

Thanks
Regards


Kaz.



-----------------------------------------
The information transmitted is intended only for the person or entity
to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this
information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the
sender and delete the material from any computer.


_______________________________________________
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