Please help

[roy999]

Hi,

I'm new to progress and need some help please. Policies is a variable which holds contract numbers. This variable is initialized with a list of contract numbers and gets incremented in the loop. It works fine when there are 10 contracts, but when i have 50000, it gives me an error. Is there another way i can do this?
The list of contract numbers are normally in an excel file. Is there a way to get the data directly from the file???

REPEAT:

FOR EACH contract_role NO-LOCK WHERE contract_number = Policies[c]

/* process*/




IF c > 9 THEN
LEAVE.
c = c + 1.

 

[TomBascom]

I don't think your sample code can possibly be complete...

An array of 50,000 elements is going to run into various memory related limits within Progress. Likewise with a comma delimited list. So don't try to do that (if that's what you're doing).

The proper method probably involves using a table. Either a temp-table if the data is coming from an external source, or a database table if the list of policies is already known.

Perhaps something like this:

/* count the number of policy-roles associated with the selected policies
 */

define variable i as integer no-undo.

for each policy no-lock where policy.pType = "xyzzy":

  for each policy-role no-lock where policy-role.pNum = policy.pNum:

    i = i + 1.

  end.

end.

display i.

 

[roy999]

I managed to figure out how to use the temp tables. I had a problem though where it would import a blank row after the last row of data was imported, so i used an IF statement to get rid of that problem.
Thats how i ended up with this block of code. And it actually works.
It imports the csv file, then retrieves the corresponding contract ref field from the DB table and then writes all the data to a txt file. Any other suggestions or comments?
I know it's not perfect, but i think it's not too bad for my 1st time.

DEFINE VARIABLE input_file AS CHARACTER NO-UNDO.
DEFINE STREAM strm1.
DEFINE VARIABLE a AS INTEGER.
DEFINE VARIABLE b AS DECIMAL.
DEFINE VARIABLE c AS INTEGER.
DEFINE TEMP-TABLE policy_details NO-UNDO  
    FIELD policy_number LIKE tbl1.contractno
    FIELD amount_requested LIKE tbl2.premium
    FIELD account_number1 LIKE tbl3.accountno
    FIELD account_holder AS CHARACTER 


c = 1.

DO:

a = ETIME(YES).

    OUTPUT STREAM strm1 TO \\outputpath.txt.

    PUT STREAM strm1 "contract_number|amount_requested|account_number1|account_holder|contract_reference" SKIP.


    ASSIGN input_file  = '\\path\policydetails.csv'.

    INPUT FROM VALUE (input_file).
    REPEAT ON ENDKEY UNDO, LEAVE:
       CREATE policy_details.
       IMPORT DELIMITER "," policy_details.
 

        FOR EACH tbl1 NO-LOCK WHERE tbl1.contractno = policy_details.policy_number
            AND tbl1_login_obj = 39017.19.

            PUT STREAM strm1 policy_details.policy_number "|" policy_details.amount_requested "|" policy_details.account_number1 "|" policy_details.account_holder 
                "|" tbl1.contractref "|" SKIP.

            IF c > 2 THEN                   /* this value should always be 1 less than the total number of contracts*/
                LEAVE.
            c = c + 1.
        END.



    END.
    INPUT CLOSE.



b = (ETIME /  1000) / 60.

    DISPLAY b "min".

END. /* end timer*/

 

[sphipp]

I managed to figure out how to use the temp tables. I had a problem though where it would import a blank row after the last row of data was imported, so i used an IF statement to get rid of that problem.

If you use REPEAT: CREATE IMPORT then the last temp-table is always blank. I normally go through the temp-table afterwards and delete blank entries.

One thing you can do is to import the data into an array variable and then create the temp-table. But, it's a lot more code and isn't really worth it. If you are importing to a database table, then it's worth doing as you can do some pre-create checks before you create the table.

Something like .....

def var temp_line as char extent 50 no-undo.
def var temp_loop as int no-undo.
def var isblank as logical no-undo.
input from afile.
repeat:
  temp_line = "".
  import delimiter "," temp_line.
  isblank = yes.
  do temp_loop = 1 to extent (temp_line):
    if temp_line [temp_loop] <> "" then do:
      isblank = no.
      leave.
    end.
  end.
  create policy_details.
  assign
    policy_details.policy_number = temp_loop [1]
    policy_details.amount_requested = temp_loop [2]
    policy_details.account_number1 = temp_loop [3]
    policy_details.account_holder = temp_loop [4].
end.
input close.