PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 08/08/2019 7:28 AM by  steve finger
Records returned in IPA using lawson query node
 13 Replies
Sort:
You are not authorized to post a reply.
Author Messages
steve finger
Senior Technical Consultant
North State Consulting
Veteran Member
(137 points)
Veteran Member
Posts:47


Send Message:

--
08/06/2019 9:08 AM
    when i do a lawson query from IPA, when the number of records specified in the query string is reached (512 by default i believe), the query seems to "skip" a large number of records before the next record returned by the query.  for example...if the "MAX" value is set to 512, the first 512 records are returned....then a number or records are skipped before the next record is returned.

    pretend i'm querying the apvenmast...  i have 10,000 vendors numbered sequentially from 1 to 10,000.

    the query has MAX=64....it returns

    1
    2
    3
    4
    ........
    63
    64
    486
    487

     

    how do i get the flow to stop skipping records....what am i doing wrong

     

    thanks in advance

     

     

     

     

    steve finger
    Senior Technical Consultant
    North State Consulting
    Veteran Member
    (137 points)
    Veteran Member
    Posts:47


    Send Message:

    --
    08/06/2019 9:09 AM

    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&

     

     

    David Williams
    Private
    Private
    Veteran Member
    (3187 points)
    Veteran Member
    Posts:1073


    Send Message:

    --
    08/06/2019 9:15 AM
    It appears your criteria, status and location, are limiting the records returned.
    David Williams
    steve finger
    Senior Technical Consultant
    North State Consulting
    Veteran Member
    (137 points)
    Veteran Member
    Posts:47


    Send Message:

    --
    08/06/2019 9:49 AM
    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....
    David Williams
    Private
    Private
    Veteran Member
    (3187 points)
    Veteran Member
    Posts:1073


    Send Message:

    --
    08/06/2019 10:02 AM
    Remove your filters and see if you get the same results.
    David Williams
    steve finger
    Senior Technical Consultant
    North State Consulting
    Veteran Member
    (137 points)
    Veteran Member
    Posts:47


    Send Message:

    --
    08/06/2019 2:13 PM

    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.

    David Williams
    Private
    Private
    Veteran Member
    (3187 points)
    Veteran Member
    Posts:1073


    Send Message:

    --
    08/06/2019 2:23 PM
    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?
    David Williams
    steve finger
    Senior Technical Consultant
    North State Consulting
    Veteran Member
    (137 points)
    Veteran Member
    Posts:47


    Send Message:

    --
    08/06/2019 2:53 PM
    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.

    this flow is for somebody who is leaving lawson and i'm tasked with extracting and reformatting the lawson data for the "new and improved" system.  the flow will have a short life span.  one good error free run and it's retired....so it doesn't need to be pretty, elegant, or efficient.  wouldn't it be nice if all programs could be so unencumbered.......

    thanks for your help, btw

    steve finger
    Senior Technical Consultant
    North State Consulting
    Veteran Member
    (137 points)
    Veteran Member
    Posts:47


    Send Message:

    --
    08/06/2019 3:04 PM

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

    David Williams
    Private
    Private
    Veteran Member
    (3187 points)
    Veteran Member
    Posts:1073


    Send Message:

    --
    08/06/2019 3:17 PM
    I’d suggest a SQL query, but otherwise yes, use the last vendor number in the next query.
    David Williams
    Lynne
    Application Specialist
    Covenant HealthCare
    Veteran Member
    (292 points)
    Veteran Member
    Posts:118


    Send Message:

    --
    08/07/2019 6:33 AM
    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=~
    Dave Curtis
    Manager, HRIS
    University of Maryland Medical System
    Veteran Member
    (389 points)
    Veteran Member
    Posts:135


    Send Message:

    --
    08/07/2019 8:05 AM
    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.
    steve finger
    Senior Technical Consultant
    North State Consulting
    Veteran Member
    (137 points)
    Veteran Member
    Posts:47


    Send Message:

    --
    08/07/2019 10:44 AM
    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
    steve finger
    Senior Technical Consultant
    North State Consulting
    Veteran Member
    (137 points)
    Veteran Member
    Posts:47


    Send Message:

    --
    08/08/2019 7:28 AM

    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

    You are not authorized to post a reply.