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

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

Richard makes a good point about iteration - because of connection
overhead, the fastest way would be to use an array...makes all the
processing happen in the program itself.  Also, it would be easier to
iterate through the selected data multiple times (where 2 items add up
to $140, where 3 items add up to $140, etc)

However, the same problem of a 'cartesian join' still exists, even
though it's not at the database...you could break your system trying to
look through all combinations - BE CAREFUL

Math fun for anyone that cares :
-----------------------------
The iterations for 100 rows in PS_ITEM trying to add up together are as
follows : 

2 items - 100 rows, each looking at 100 rows = 100 ^2 = 10000
3 items - 100 rows, each looking at 100 rows, each looking at 100 rows =
100 ^3 = 1000000
4 items - 100 rows = 100 ^4 = 100,000,000 

A hundred million iterations for any combo of 100 rows.  

Now, assume you have 500,000 rows in PS_ITEM (more likely) that you
would try to add up together, looking for 2, 3, 4 combinations : 

500000 ^2 + 500000 ^3 + 500000 ^4 =
62500000000000000000000 + 125000000000000 + 2500000000 =

62,500,000,125,002,500,000,000 itterations...they don't even make a word
for how many digits that is...your system admin wouldn't be overly happy
with that.  


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of
Knapp, Richard
Sent: Thursday, December 08, 2005 10:09 AM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] Need Some help in a SQR Select


Hmmm - I was _ass_uming that it was any combination of rows with a
variable number of total rows to equal the target number.  This suggests
an iterative solution which is why I thought an array would do the
trick.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Bob Stone
Sent: Thursday, December 08, 2005 10:02 AM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: RE: [sqr-users] Need Some help in a SQR Select

...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.
>


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