Hello All,

I have a list of purchase orders on System A (> SQL Where Limit), I want to pull from System B where that PO exists. The file on System B is VERY large, so joining these two together is not an option. We are trying to batch the PO#'s in the where statement with about 500 at a time. Here is as far as we have gotten.

I apologize, but this is our first time trying some looping and reading logic this way:

** This example will send one PO at a time, how would we send say 500 or 1000 at a time in the SQL WHERE statement?

  

-* File 14_build_Invoice.fex

APP PREPENDPATH innermargin
APP HOLD innermargin

TABLE FILE PO_DEAL_COMPLETE
SUM
COMPUTE RANK1/I9 = IF (PONUMB EQ LAST PONUMB) THEN 1 ELSE RANK1 + 1;
BY  LOWEST PO_DEAL_COMPLETE.SEG01.PONUMB
WHERE READLIMIT EQ 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END


-SET &LIMIT=&LINES;


FILEDEF RESULTS DISK results.ftm (APPEND

-RUN


-REPEAT :ENDLOOP &LIMIT TIMES
-READFILE HOLD1

TABLE FILE INVOICEDET
BY  LOWEST INVOICEDET.INVOICEDET.DDPON
WHERE INVOICEDET.INVOICEDET.DDPON EQ '&PONUMB';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS RESULTS FORMAT ALPHA
END
-RUN
-:ENDLOOP

TABLE FILE RESULTS
PRINT *
END

There was a topic like this back in 2008 that helped us get this far:

LOOP IN FILE

This message has been edited. Last edited by: John C.,

You can use

WHERE INVOICEDET.INVOICEDET.DDPON IN FILE HOLD1;

I'm not sure how that performs with the entire file's worth of identifiers (probably badly or not at all).

If you create that file in batches of 500 + remainder, that should remedy that part of the issue.

For example, you could write the entire file of ID's to a hold file that includes a record number (1, 2, 3, ..., 1238120398) and copy that file in 500-line batches to an alpha file that you then use for the WHERE IN FILE clause.

quote:

Originally posted by Wep5622:
You can use

WHERE INVOICEDET.INVOICEDET.DDPON IN FILE HOLD1;

I'm not sure how that performs with the entire file's worth of identifiers (probably badly or not at all).

If you create that file in batches of 500 + remainder, that should remedy that part of the issue.

For example, you could write the entire file of ID's to a hold file that includes a record number (1, 2, 3, ..., 1238120398) and copy that file in 500-line batches to an alpha file that you then use for the WHERE IN FILE clause.

Yeah the way we have it now it works, but not well. We are looking to make the hold files in batches of 500 or 1000 in the IN FILE. We just can't figure out how to systematically do this based on the number of records in the file.

In the futrue we are going to have to use this method a lot, so we need to try and get a working example.

Thank you!

JC

Something like this should do it:

-* 1. hold all keys
TABLE FILE PO_DEAL_COMPLETE
  SUM COMPUTE SEQ/I9=SEQ+1;
  BY  LOWEST PO_DEAL_COMPLETE.SEG01.PONUMB
  ON TABLE HOLD AS HOLD0
END
-RUN
-SET &X_LINES=0+&LINES;

-* 2. break into a series of hold files, (&X_SIZE) keys each
-SET &X_SIZE=500;
-SET &X_ITERS = INT ( (&X_LINES + &X_SIZE - 1 ) / &X_SIZE );

-REPEAT LOOP1 FOR &X FROM 1 TO &X_ITERS ;  
-SET &X_HI = &X_SIZE * &X ;
-SET &X_LO = &X_HI + 1 - &X_SIZE ;
TABLEF FILE HOLD0
  PRINT PONUMB
  WHERE ( SEQ GE &X_LO ) AND ( SEQ LE &X_HI );
  ON TABLE HOLD AS &HOLD&X FORMAT ALPHA
END
-LOOP1

-* 3. pull results, combining extracts for each of the respective hold files

TABLE FILE INVOICEDET
  PRINT items_of_interest
  BY  LOWEST INVOICEDET.INVOICEDET.DDPON
  WHERE INVOICEDET.INVOICEDET.DDPON IN FILE 'HOLD1' ;
  ON TABLE HOLD AS RESULTS

-REPEAT LOOP2 FOR &X FROM 2 TO &X_ITERS ;
MORE
FILE INVOICEDET
  WHERE INVOICEDET.INVOICEDET.DDPON IN FILE 'HOLD&X' ;
-LOOP2

END
-RUN

TABLE FILE RESULTS
PRINT *
END

[edited: -REPEAT &LOOP1]

This message has been edited. Last edited by: j.gross,

Jack,

Thank you for the reply! We put your code into the fex and got the following error in the hold file loop logic:

  
 0 NUMBER OF RECORDS IN TABLE=     5076  LINES=   5076
 0 ERROR AT OR NEAR LINE     19  IN PROCEDURE 14_build_invoice.f
 (FOC295) A VALUE IS MISSING FOR: &LOOP1

This looked like it worked perfectly. Jack we owe you sir.

  

-* 1. hold all keys
TABLE FILE PO_DEAL_COMPLETE
  SUM COMPUTE SEQ/I9=SEQ+1;
  BY  LOWEST PO_DEAL_COMPLETE.SEG01.PONUMB
  ON TABLE HOLD AS HOLD0
END
-RUN
-SET &X_LINES=0+&LINES;

-* 2. break into a series of hold files, (&X_SIZE) keys each
-SET &X_SIZE=500;
-SET &X_ITERS = INT ( (&X_LINES + &X_SIZE - 1 ) / &X_SIZE );

-REPEAT LOOP1 FOR &X FROM 1 TO &X_ITERS ;
-SET &X_HI = &X_SIZE * &X ;
-SET &X_LO = &X_HI + 1 - &X_SIZE ;
TABLEF FILE HOLD0
  PRINT PONUMB
  WHERE ( SEQ GE &X_LO ) AND ( SEQ LE &X_HI );
  ON TABLE HOLD AS HOLD&X FORMAT ALPHA
END

-LOOP1

-* 3. pull results, combining extracts for each of the respective hold files

TABLE FILE INVOICEDET
  SUM DDPON
  BY  LOWEST INVOICEDET.INVOICEDET.DDPON
  WHERE INVOICEDET.INVOICEDET.DDPON IN FILE 'HOLD1' ;
  ON TABLE HOLD AS RESULTS

-REPEAT LOOP2 FOR &X FROM 2 TO &X_ITERS ;
MORE
FILE INVOICEDET
  WHERE INVOICEDET.INVOICEDET.DDPON IN FILE 'HOLD&X' ;
-LOOP2

END
-RUN

TABLE FILE RESULTS
PRINT *
END

Your welcome.

I suggest you add
STEP 1
to the second -REPEAT, to ensure that the loop will be skipped if &X_ITERS is 1 (only one "batch" worth of keys).

quote:

Originally posted by j.gross:
Your welcome.

I suggest you add
STEP 1
to the second -REPEAT, to ensure that the loop will be skipped if &X_ITERS is 1 (only one "batch" worth of keys).

Noted, thank you again. Hope you had a great weekend. Going to mark this solved.