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

["John Tucker" <john.tucker@oracle.com>]

The problem with your current statement, as I see it, is that it's trying to 
sum just one row.  That is, the "sum" you ask for would be identical to the 
bal_amt of each individual row.

I don't know the ps_item table, so here's some pseudo-code for what I'd try.

Begin-Select
a.item
b.item

from ps_item a, ps_item b
where [some key between a & b doesn't match]
and a.balance_amt + b.balance_amt = 140
End-Select

This should return two items whose balance amount adds up to 140.  Make sure 
you do something like "a.item > b.item" when you're checking to make sure one 
of the keys doesn't match.  If you just use <> on all of them, you'd end up 
with duplicate rows.  That is, to use your data below, you'd get one row of 
XYZ, KKK and a second row of KKK, XYZ.
 
-John Tucker

PS - You might want to avoid using "KKK" as an example in future postings.  If 
you don't already know what it stands for, this isn't the place for me to 
educate you, though.

-----Original Message-----
From: Kaz.Narayanan@averydennison.com
[mailto:Kaz.Narayanan@averydennison.com]
Sent: Thursday, December 08, 2005 1: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