Sorting > 1000 array elements

[wanderingsmith]

I have a subreport that goes through our database of parts and their transactions to calculate a Turnover ratio for each part. I store the part numbers in string array TurnoverPN, and at the same [element address] in number array Turnover, I store my calculated ratio.

At the end of the SubReport (or back in the main report at the same time as I go through the results of other subreports to compute a cycle count frequency rating, I don't care), I want to go through the ratios and order them (and their p/n) so that I can then identify the top 10% and 20%.

Due to, even after ignoring those with ratio of zero, there still being more than 1k parts, I'm storing the first 999 in Turnover, and then switching to Turnover2 (similarly, TurnoverPN and TurnoverPN2).

I tried doing a nested for loop sorting using the maximum() function and it squawked 'A loop was evaluated more than the maximum number of times allowed'. I've since found a reference that dual for sorting should only be used for arrays of 150 elements or less.. Ok. So what do I use for larger ones? I, of course, would really rather not have to create 20 arrays and work through them... though come to think.. pretty sure that'd still trigger the same error.

Any help'd be appreciated

 

[lbass]

Have you ruled out using a topN/group sort on the calculated ratio? What is the content of that formula?

-LB

 

[wanderingsmith]

If I understand what you are asking correctly:
The subreport is currently grouped first by p/n, then by transaction date, then by transaction ID.
The transacted QTYs are conditionally summed at the transaction ID level.
The results are then used, at the Part ID level, to calculate Turnover ratio.
And in the main report, this is only one of three subreports (all facing this same challenge) that will garner sets of data, each needing to be sorted.

Am I missing a way to use group sort?

 

[lbass]

Please show the content of the calculation formula. You won't be able to sort the main report by subreport data, however, if that is what you are hoping for.

-LB

 

[wanderingsmith]

//                 cost of goods issued
// -----------------------------------------------------
// ( ( start inventory cost + end inventory cost ) / 2 )

if  ( ({PART.QTY_AVAILABLE_ISS}-{#PeriodChanges}) + {PART.QTY_AVAILABLE_ISS} ) = 0
then 0
else
(
    if {@1Cost} = 0
    then
    (    
        {#PeriodIssue}
        / 
            ( 
                Abs(
                    ({PART.QTY_AVAILABLE_ISS}-{#PeriodChanges}) + {PART.QTY_AVAILABLE_ISS}
                )/2
            )
    )
    else
    (    
        ( {#PeriodIssue} * {@1Cost} )
        / 
            ( 
                Abs(
                    (
                        ({PART.QTY_AVAILABLE_ISS}-{#PeriodChanges}) + {PART.QTY_AVAILABLE_ISS}
                    )*{@1Cost}
                )/2
            )
    )
)

 

[lbass]

You won't be able to use this for sorting because you are using running totals, which evaluate across a set of records. Since I don't know how the two running totals are set up or the content of {@1cost}, I can't tell whether you could easily convert these to SQL expressions. You would need to have the formula as a whole available for an inserted summary if you want to be able to do a group sort.

-LB

 

[wanderingsmith]

- Cost is just a sum of 4 fields in the table
- the running totals are of a formula:

if {INVENTORY_TRANS.TYPE} = "I" 
then {INVENTORY_TRANS.QTY} 
else 0-{INVENTORY_TRANS.QTY}

and another formula almost identical (just slightly different condition)

I take it there is no array sorting that will flex to large size?

Do you have a book you can recommend as reference/learning tool for T-SQL?

 

[lbass]

I don't have a facility with arrays, and I find them unwieldy, so I can't answer that question or your last one.

If you want to try a method that doesn't rely on arrays, please explain your running totals further--are you summing the formula on change of some group? If so, what group? It would help to see your SQL query (database->show SQL query).

-LB

 

[wanderingsmith]

Both are evaluated For each record and reset On change of group Part.ID. The formulas themselves are in the detail (only for the calculations. nothing in this sub actually has to show in the resulting report).

SELECT "PART"."ID", "INVENTORY_TRANS"."TRANSACTION_DATE", "INVENTORY_TRANS"."TRANSACTION_ID", "INVENTORY_TRANS"."TYPE", "INVENTORY_TRANS"."QTY", "INVENTORY_TRANS"."CLASS", "PART"."UNIT_MATERIAL_COST", "PART"."UNIT_LABOR_COST", "PART"."UNIT_BURDEN_COST", "PART"."UNIT_SERVICE_COST", "PART"."TOOL_OR_FIXTURE", "PART"."CONSUMABLE", "PART"."STATUS", "PART"."PURCHASED", "PART"."QTY_AVAILABLE_ISS"
FROM "ourdb"."dbo"."PART" "PART" LEFT OUTER JOIN "ourdb"."dbo"."INVENTORY_TRANS" "INVENTORY_TRANS" ON "PART"."ID"="INVENTORY_TRANS"."PART_ID"
WHERE "PART"."STATUS" IS NULL AND "PART"."CONSUMABLE"<>'Y' AND "PART"."TOOL_OR_FIXTURE"<>'Y' AND "PART"."PURCHASED"='Y' AND ("INVENTORY_TRANS"."TRANSACTION_DATE">={ts '2009-08-30 00:00:00'} AND "INVENTORY_TRANS"."TRANSACTION_DATE"<{ts '2010-08-30 00:00:01'})
ORDER BY "PART"."ID", "INVENTORY_TRANS"."TRANSACTION_DATE", "INVENTORY_TRANS"."TRANSACTION_ID

 

[lbass]

I'm sorry, I think the solution to this is beyond me. Perhaps someone who is more proficient with arrays could help you with your original approach.

-LB

 

[wanderingsmith]

thanks anyway