Records returned in IPA using lawson query node

here's my query string:

 

PROD=LSAPPS&FILE=APVENMAST&INDEX=VENSET1&FIELD=VENDOR;VENDOR-VNAME;VENDOR-SNAME;CREATE-DATE;VENDOR-STATUS;VENDOR-GROUP;ADDRESS1.ADDR1;ADDRESS1.ADDR2;ADDRESS1.ADDR3;ADDRESS1.ADDR4;ADDRESS1.CITY-ADDR5;ADDRESS1.STATE-PROV;ADDRESS1.POSTAL-CODE;ADDRESS1.COUNTY;ADDRESS1.COUNTRY-CODE;APVENLOC.LOC-TYPE;APVENLOC.LOCATION-CODE&SELECT=VENDOR-STATUS=A%26APVENLOC.LOCATION-CODE=&OUT=CSV&DELIM=&MAX=64&

 

 

It appears your criteria, status and location, are limiting the records returned.

i was trying to limit the number of records returned. my problems is that once the MAX number of records is returned, the next record returned is not really the next record......a large number of intervening records is skipped. If i change my MAX value in the query string to 16, the first 16 records are returned....and then the next record returned might actually be what should have been the 147th record....

Remove your filters and see if you get the same results.

same result.  the weird thing to me is this:  after the number of records specified by the "MAX" value in the query string, the next record returned should be returned much later.  check this out:

 

query string: PROD=LSAPPS&FILE=APVENMAST&FIELD=VENDOR;VENDOR-STATUS;VENDOR-GROUP;VENDOR-VNAME&OUT=CSV&MAX=5&DELIM=

results in msgbuilder: 

Activity started: MsgBuilder7030  (Run Id: 1305)
Variables in Process:

var1 =        11 |I|1|@COMM CORPORATION
      104 |I|1|A BETTER VIEW GLASS&MIRROR,INC
      110 |A|1|A-1 NURSING REFERRALS, INC
      120 |I|1|A&T STATE UNIVERSITY
      122 |I|1|A BOLDER IMAGE
999000001 |D|1|BLANCO TACKABERRY COMBS MATA
999000002 |D|1|HARRY A. BOLES
999000003 |D|1|PEEBLES LAW FIRM
999000004 |D|1|RICHARD M. PEARMAN JR.
999000005 |D|1|SAINTSING PLLC
999000006 |D|1|FIRST NATIONAL BANK & TRUST
999000007 |D|1|FIRM AT FISHER PARK
999000008 |D|1|FISERV LENDING SOLUTIONS
999000009 |D|1|WASLAW LLC
999000010 |D|1|WACHOVIA BANK
999000011 |D|1|DESERT CAPITAL TRUST
999000012 |D|1|CHERYL DAVID
999000013 |D|1|CLOSELINE LLC
999000014 |D|1|UCC DIRECT SERVICES
999000015 |D|1|BANK OF AMERICA

now i change MAX to 10:  PROD=LSAPPS&FILE=APVENMAST&FIELD=VENDOR;VENDOR-STATUS;VENDOR-GROUP;VENDOR-VNAME&OUT=CSV&MAX=10&DELIM=

results in msgbuilder:

var1 =        11 |I|1|@COMM CORPORATION
      104 |I|1|A BETTER VIEW GLASS&MIRROR,INC
      110 |A|1|A-1 NURSING REFERRALS, INC
      120 |I|1|A&T STATE UNIVERSITY
      122 |I|1|A BOLDER IMAGE
      123 |A|1|A BRIGHT START CCLC,INC
      127 |I|1|A & H SERVICES LLC
      130 |A|1|A & A PLANTS, INC
      148 |I|1|ABAG TRAINING CENTER
      164 |I|1|A C BAILEY
999000001 |D|1|BLANCO TACKABERRY COMBS MATA
999000002 |D|1|HARRY A. BOLES
999000003 |D|1|PEEBLES LAW FIRM
999000004 |D|1|RICHARD M. PEARMAN JR.
999000005 |D|1|SAINTSING PLLC
999000006 |D|1|FIRST NATIONAL BANK & TRUST
999000007 |D|1|FIRM AT FISHER PARK
999000008 |D|1|FISERV LENDING SOLUTIONS
999000009 |D|1|WASLAW LLC
999000010 |D|1|WACHOVIA BANK
999000011 |D|1|DESERT CAPITAL TRUST

last time - change MAX to 13:  PROD=LSAPPS&FILE=APVENMAST&FIELD=VENDOR;VENDOR-STATUS;VENDOR-GROUP;VENDOR-VNAME&OUT=CSV&MAX=13&DELIM=

msgbuilder:

var1 =        11 |I|1|@COMM CORPORATION
      104 |I|1|A BETTER VIEW GLASS&MIRROR,INC
      110 |A|1|A-1 NURSING REFERRALS, INC
      120 |I|1|A&T STATE UNIVERSITY
      122 |I|1|A BOLDER IMAGE
      123 |A|1|A BRIGHT START CCLC,INC
      127 |I|1|A & H SERVICES LLC
      130 |A|1|A & A PLANTS, INC
      148 |I|1|ABAG TRAINING CENTER
      164 |I|1|A C BAILEY
      170 |A|1|A A WORLD SERVICES
      180 |A|1|A HELPING HAND HOME CARE,LLC
      181 |A|1|A GREAT START CHILD DEVEL
999000001 |D|1|BLANCO TACKABERRY COMBS MATA
999000002 |D|1|HARRY A. BOLES
999000003 |D|1|PEEBLES LAW FIRM
999000004 |D|1|RICHARD M. PEARMAN JR.
999000005 |D|1|SAINTSING PLLC
999000006 |D|1|FIRST NATIONAL BANK & TRUST

 

the default value for MAX is 512.  if i leave out the MAX value, the "skip" in the records occurs after 512 records.

Okay, so the MAX value should only return that number of records period. Are you looping back to the query to try to pick up the next 5 (10, 15, 512) records? If so, you're confusing the system. If you are looping back, is there a Branch or User Action between?

yes...there are several actions going on between records.  the end goal is an interface file with all active vendors (from apvenmast).  there are a few other fields from other tables that need to be collected before i write the text record for the interface (via msgbuilder).  APVENMAST has 190k records of which 40k are active vendors.  should i be using a transaction node (with "next" actions) to cycle through the vendor file instead of a query?  or dump a list of the vendors to a text file and use that list as an input file?  i read that the query has a limit (10k records?) such that i can't get all the records in a single query.

thanks for your help, btw

or perhaps i should remember the last "good" record that was returned and restart my  query at that record plus one?

I’d suggest a SQL query, but otherwise yes, use the last vendor number in the next query.

When I query files that are too large and limited by 10K, I use NEXT=TRUE to pull them all. Mine aren't as large as yours but it works.
...&OUT=CSV&NEXT=TRUE&DELIM=~

Just a thought but maybe it is a sorting issue. Maybe try using an index so to assure it is sorting the same each time it is run.

I added NEXT=TRUE to my query string and it seemed to make no difference. i deleted the MAX part of the query string and added NEXT=TRUE. the 513th record was the beginning of the out of sequence mess....as the 512 default reared its ugly head. is there more that needs to be done?
the query string above was simplified for demonstration....the real query string specifies an index, for what that's worth

dave....i tried to send you a private message but the guru kept throwing me an error and your contact info is hidden.  could you contact me at stevefinger1@gmail.com.  nothing real important but meaningful to me.

 

thanks