ACTRANS, APDISTRIB, CBDISTRIB, & GLTRANS Query with duplicates

 6 Replies
 0 Subscribed to this topic
 1 Subscribed to this forum
Sort:
Author
Messages


Sherry Shimek











Advanced Member



Posts: 43




Advanced Member



    One of our users has queries set up by a former consultant that uses ACTRANS as the base table and fields from related tables APDISTRIB, CBDISTRIB, and GLTRANS.

    Prior to running the queries this month, the user was getting correct data.  She doesn't remember making any changes to the query and now she is getting multiple duplicate records.  The file was only 6k lines last month and now is 60k.

    Are there indices or other parameter settings that restrict the data to not pull duplicates??? I don't create queries linking multiple transaction and distribution tables in MS Addins because of this issue, so I am not able to help. 

    .dme is attached.

    User stated query has always been 400 OTM. 

    Fields in query are

    COMPANY
    ACCT-UNIT
    ACCOUNT
    SUB-ACCOUNT
    ACTIVITY
    ACCT-CATEGORY
    FISCAL-YEAR
    PERIOD
    POSTING-DATE
    SYSTEM
    GLTRANS.JE-TYPE
    GLTRANS.CONTROL-GROUP
    REFERENCE
    DESCRIPTION
    TRAN-AMOUNT
    APDISTRIB.DESCRIPTION
    APDISTRIB.INVOICE
    APDISTRIB.ORIG-TRAN-AMT
    CBDISTRIB.DESCRIPTION
    CBDISTRIB.ORIG-BASE-AMT
    RESOURCE-DESC
    RESOURCE-CODE
    SUM-ACCT-CAT
    TRANS-TYPE
    RUN-DATE
    TRAN-DATE
    CAPITALIZE-FLG
    ACTIVITY-GRP
    AC191-UPDATED

     

     

    Sherry Shimek Catholic Health Initiatives Englewood CO


    Derek Czarny











    Veteran Member



    Posts: 63




    Veteran Member



      Duplication results usually come from bad joins, especially when the expected result set expands 10 times as much as expected. Did anyone change the relationships in dbdef for those tables? Where there any patches applied to the system that effected the database?


      Sherry Shimek











      Advanced Member



      Posts: 43




      Advanced Member



        Hi, Derek. I know of at least one CTP that was loaded. I am checking to see if that would have affected the dbdef.

        If it did and even if it didn't, is there a utility program that we need to run to rebuild the db? And it the db is okay and we run it, will it cause problems or will it just ensure the integerity of the db?

        Thanks,
        Sherry Shimek Catholic Health Initiatives Englewood CO


        Derek Czarny











        Veteran Member



        Posts: 63




        Veteran Member



          If there is a problem with the relationships, that doesn't necessarily mean their is a problem with your raw data. It is just the output that is getting duplicated. You could test that by just querying the ACTRANS table with what ever parameters are in your original query for that table and look for duplicates. Then add each related table one at a time until you see the duplication. I would start with ACTRANS to GLTRANS relationship. That one is pretty straight forward and you shouldn't see duplication.


          Ruma Malhotra











          Veteran Member



          Posts: 412




          Veteran Member



            I do know that the actrans table has a field called the ATN obj id that should be linked to  the apdistrib table by the same field which is the apd_atn_obj_id and has a one to many relationship which may cause multiple records.



            Sherry Shimek











            Advanced Member



            Posts: 43




            Advanced Member



              Thank you all for your suggestions.

              Lawson is creating a patch to correct this issue in our Production environment that according to the support person was corrected is LSF 9.0.0.7 and higher versions. We are in the process of moving to a higher version of LSF9 and in our preliminary tests we ran into a different error that we will present to Lawson. I'll update this ticket when we know more.
              Sherry Shimek Catholic Health Initiatives Englewood CO


              Sherry Shimek











              Advanced Member



              Posts: 43




              Advanced Member



                Update on this issue. We are now on MS Addins version 9.0.0.2055 recently developed by Lawson that corrected this issue for us.

                It is handling the multitable multiple one-to-many queries without any duplicate records.

                Thank you everyone for your suggestions and help with this.

                Sherry Shimek Catholic Health Initiatives Englewood CO