ACTRANS, APDISTRIB, CBDISTRIB, & GLTRANS Query with duplicates -

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1375
Roger French	1315
mark.cook	1244

Sherry Shimek

Advanced Member Send Private Message

Posts: 43

1/20/2010 7:52 PM

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

Derek Czarny

Veteran Member Send Private Message

Posts: 63

1/21/2010 12:20 PM

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 Send Private Message

Posts: 43

1/21/2010 2:43 PM

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,

Derek Czarny

Veteran Member Send Private Message

Posts: 63

1/21/2010 3:39 PM

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 Send Private Message

Posts: 412

1/21/2010 8:00 PM

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 Send Private Message

Posts: 43

2/9/2010 6:41 PM

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

Advanced Member Send Private Message

Posts: 43

3/19/2010 7:03 PM

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.