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
/* process*/
IF c > 9 THEN
LEAVE.
c = c + 1.
Royston,
What error message are you receiving?
If you are accumulating them in a comma delimited list, the error message probably has to do with exceeding the 32K limit on a character variable. You could change to longchar, assuming a recent enough version of Progress, but I suspect this isn't the best design. Have you considered a temp-table?
Hi,
The error has to do with the size. I've considered a temp table, but i dont know how to "point" progress to the location of the file.
What file? The temp-table is defined and filed and used within your code.
Perhaps it would help if you described a little more about the whole process so that we could talk about it in context.
Save your excel file to a tab delimited txt. Then do:
DEFINE VARIABLE cLine AS CHARACTER NO-UNDO.
DEFINE TEMP-TABLE ttContract NO-UNDO
FIELD firstField AS CHARACTER
FIELD secondField AS CHARACTER
INDEX idxPrim IS PRIMARY firstField secondField.
INPUT FROM "path/to/excelfile".
REPEAT ON ERROR UNDO, LEAVE:
IMPORT UNFORMATTED cLine.
CREATE ttContract.
ASSIGN ttContract.firstField = ENTRY(1, cLine, "~t":U)
ttContract.secondField = ENTRY(2, cLine, "~t":U).
END.
INPUT CLOSE.
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*/