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

["Bob Stone" <bstone@fastenal.com>]

...what you want to do is join the table to itself...and see if the sum
of 2 rows' balance amounts = $140.

Now (take this suggestion at your own risk!!) I think a Cartesian join
from the table to itself would give you what you want (...but I don't
think you want to do that).  Unless the table has a SMALL number of
rows, that join will get out of hand QUICK. 

Maybe narrow it down to only look at rows from either table where the
balance amount < $140 (because $150 + (other pos amt) > $140...it's
math).  If you have negative balances...that doesn't work. I include the
'distinct' because the row would show up twice...once for the join in
each direction.


...now if you want to have 3 rows add up to $140, ($60, $70, $10) try a
triple Cartesian join.  You may get a DBA to come kick you in the
...uh...well, I'm sure it'd be some place unpleasant.  If you don't
understand why he'd be upset, ask him BEFORE you run it.  He can explain
the problem.


SELECT DISTINCT a.item, b.item
FROM ps_item a, ps_item b
WHERE a.bal_amt + b.bal_amt = 140
  AND b.bal_amt < 140              ! optional 
  AND a.bal_amt < 140              ! optional

-- repeat any 'narrow down' criteria (such as each bal < 140) for each
table, BUT DON'T JOIN THEM.

Having said all of that, THIS IS A BAAAAD IDEA.  DON'T DO THIS.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of
Daniel Vandenberg
Sent: Thursday, December 08, 2005 9:47 AM
To: This list is for discussion about the SQR database reporting
language from Hyperion Solutions.
Subject: Re: [sqr-users] Need Some help in a SQR Select

In your example, your SQL would not select any rows because no item has
a 
total of 140.00.  Because you group by ITEM, it only checks the sums of 
rows with the same values for ITEM.

If  you had the following data, your SQL should return XYZ for the ITEM.

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

It would find the total of XYZ (rows 1 and 3) and select it because the
sum 
is 140.00 and find the total of LMN (row 2) and not select it because it
is 
125.00.  Those are the only two totals it would check.   In your
example, 
each row has a different ITEM, so each sum would include only one row
and 
none of them would be 140.

At 12:33 AM 12/8/2005, you wrote:
>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


Daniel Vandenberg
Administrative Computing
University of Wisconsin Oshkosh

Email :  vandberg@uwosh.edu 


_______________________________________________
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