RE: [sqr-users] Dynamic Array Size - Version 6.2

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

You can use substitution variables, but that doesn't allocate dynamically.
The substition takes place as the compiler is processing the source file,
long before the SQL will execute.  Once the substitution takes place, the
resulting text is interpreted by the compiler.  The compiler will still
insist that the array size be a literal number, not a variable.

The latest version of SQR allows you to have extensions to the array like
extensions to the load-lookup command.  I wish I had the latest version.  It
seems like that gives you the same effect as a dynamic array, but I don't
know how it works.

-----Original Message-----
From: Jessie Dickenson [mailto:jmdickenson@rainbow-media.com]
Sent: Wednesday, September 07, 2005 11:11 AM
To: bstone@fastenal.com; This list is for discussion about the SQR
database reportinglanguagefrom Hyperion Solutions.
Subject: RE: [sqr-users] Dynamic Array Size - Version 6.2


Thank you Bob for your prompt response. 

I am actually over-allocating the storage; but figured there maybe a cleaner
way to deal with the array size. While searching the archives of this forum,
I found a solution that it is possible to allocate the size dynamically by
using substitution variables. 

While I do have a solution by over-allocating for the agenct count at comple
time, I would still like to know if this dynamic allocation really works in
6.2.

Thanks again.
Jessie

>>> bstone@fastenal.com 09/07/05 1:59 PM >>>
did you try assigning the variable a value?  Right now, you are not.
Create-Array needs a value at compile time, and AgentCount is set at
runtime.  So as it compiles it see 'create array of size null -- ERROR'.  

If you don't know how many rows you have, you must over-allocate and specify
an upper bound.  If you think you'll have between 60-80 agents usually,
maybe set the array size to 100.  2 months down the road you end up with
more agents, change the array size to 500.  The difference to your machine
between an array of 100 and 500 and 5000 is hardly noticable for an SQR.  

Go ahead and over-allocate, and if you need to know the number of agents (so
you can cycle through them in the array, or some other reason besides
creating the array of that size) you can either count as you're inserting,
or do what you do with #agent_count.  Within the program, using while loops
and #agent_count, you can basically pretend that the array is only as big as
#agent_count...even though more space was allocated at compile time.



-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org 
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Jessie Dickenson
Sent: Wednesday, September 07, 2005 11:59 AM
To: sqr-users@sqrug.org 
Subject: [sqr-users] Dynamic Array Size - Version 6.2


I need help with dynamic array sizing. 

Here is my situation:
I need to create an array & assign the size to a number based on the total
count of agents in a given geographic area.

So, I do the following:
1. I determine the number of agents & assign that count to a variable,
#Count.
2, Then, I use this variable in the #Define command to assign this value to
a substitution variable AgentCount.
3. Then, I use this substitution variable, AgentCount for the size parameter
within a Create-Array command.

I get the following SQR error: (SQR 3526) Missing or incorrect SIZE= in
CREATE-ARRAY.

While debugging this, I find the correct value assigned to the substitution
variable, AgentCount. However the Create-Array fails. 

I cannot get the agent count within the setup portion, since the count is
returned by a PL/SQL procedure.

Can somebody let me know if I am doing something incorrectly. 

Thanks in advance.
Jessie

BEGIN-PROCEDURE Get_Agent_Count
BEGIN-SELECT ON-ERROR=SQR_ERROR((1,'Get_Agent_Count')
COUNT (DISTINCT MY_TBL.AgentLENGTH)     &Count

        Let #Agent_Count = &Count
FROM 
        MY_SCHEMA.MY_TBL
WHERE 
        MY_TBL.RPT_RUN_HDR_ID           = #rpt_run_hdr_id
END-SELECT

#Define AgentCount #Agent_Count
        
Create-Array Name=Arr_Totals size={AgentCount}  
END-PROCEDURE Get_Agent_Count


_______________________________________________
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


_______________________________________________
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