Re: [sqr-users] Array Sort/Grouping Help

[Darrel Scott <darrel_1977@yahoo.com>]

Hello all....
 
Based on what's been sugessted (and heavy trial and error), here's what my code 
looks like now...
 
Begin-Procedure Build-Array
 Let #i = 0
 Let $Exists = 'N'
 
 While #i < #ArrayCtr

  If #ID = ID_Array.ID(#i)
   Array-Add #NBR to ID_Array(#i) NBR
   Let $Exists = 'Y'
   Let #i = #ArrayCtr
  End-If
  Add 1 to #i

 End-While
 
 If $Exists = 'N'
  PUT #ID #NBR INTO ID_Array(#ArrayCtr)
   ID NBR
  Add 1 to #ArrayCtr
 End-If

End-Procedure
 
I call the above procedure from within the select statement in which I gather 
all my IDs and their appropriate sum(NBRs). When I'm ready to send my data to 
an output file, I pass my logic from within a second While loop (similar to the 
one in my Build-Array procedure).
 
I've throughly tested this and I get exactly what I need.
 
Thank you all for your help.
 
- Darrel


George Jansen <GJANSEN@aflcio.org> wrote:
I continue to suspect you are doing this the hard way, and that there is
a simpler solution. However, your immediate problem is that SQR arrays
are dense and your IDs are sparse. You can map the sparse IDs onto the
dense array using LOAD-LOOKUP/LOOKUP: the KEY of the lookup table will
be the ID#, the RETURN_VALUE will be some arbitrary but unique number
such as Oracle's ROWNUM pseudo-column. Then when you go to stash an NBR> pair 
in the array, you will first lookup the array index (the
ROWNUM), then use array-add to sum.

The following pointless SQR illustrates this method using a view built
as
CREATE VIEW DISTINCT_COLUMNS AS 
SELECT DISTINCT COLUMN_NAME FROM USER_TAB_COLUMNS



Begin-Report

Do init-array
Do with-tables
Do show-sums

End-Report

Begin-Setup
Load-Lookup
Name=ids
Table=distinct_columns
Key=column_name
Return_Value=rownum

Create-Array name=cols size=50
field=id:char
field=sum_of_length:number=0
End-Setup

Begin-Procedure init-array
let #i = 0
while #i < 50
put 0 into cols(#i) sum_of_length
let #i = #i + 1
end-while
End-Procedure

Begin-Procedure with-tables
Begin-Select
table_name
do with-columns(&table_name)
>From user_tables
End-Select
End-Procedure

Begin-Procedure with-columns($table_name)
Begin-Select
column_name
data_length 
lookup ids &column_name $idx
let #idx = to_number($idx)
array-add &data_length to cols(#idx) sum_of_length
put &column_name into cols(#idx) id ! idempotent, isn't that the
word?
>From user_tab_columns
Where table_name = $table_name
End-Select
End-Procedure

Begin-Procedure show-sums
let #i = 0
while #i < 50
get $colname #sum from cols(#i)
if #sum > 0
display $colname noline
display ' -> ' noline
display #sum
end-if
let #i = #i + 1
end-while
End-Procedure

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

---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!

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