NON unique keys in Z501 and Z20

• Article Type: General
• Product: Aleph
• Product Version: 18.01

Description:
In regard to the ABC50 Z20 and Z501: during an imp_current_library I received an unable to index due to duplicates in the Z20_REC_KEY and Z501_REC_KEY.

Is this a problem or just an informational message? If a problem how do I repair? No other errors showed up during the exp_current_library and imp_current_library runs.

Ran checks in oracle
Z20 has 22773 records with 204 duplicated Z20_REC_KEYs
Z501 has 3479 records with 633 duplicated Z501_REC_KEYSs

Resolution:
Yes, these errors indicate duplicate unique keys.
I've checked z20 and z501 records: looks like they were loaded twice. Are you running a job that creates mono and serial claims?

Later:

You can find out duplicate keys in sql:

select count (*),Z20_REC_KEY from z20
group by Z20_REC_KEY
order by 1 desc;

COUNT(*) Z20_REC_KEY
---------- --------------------
11 00001962400003000001
5 00000109100178000001
5 00000108900150000001
4 00004475700069000001
4 00000422900017000001
..........


There are too many duplicate Z20_REC_KEY in ABC50

Z20_REC_KEY is a combination of adm doc number, item sequence and claim sequence:


z20_rec_key \
doc_number
item_sequence
claim_sequence



I belive that a problem is on the claim_sequence level.

I'd suggest that you contact support.


The same problem is with the z501 records: looks like that these records have been loaded twice (?):


select count (*),Z501_REC_KEY from z501
group by Z501_REC_KEY
order by 1 desc;

**** Hit return to continue ****

COUNT(*) Z501_REC_KEY
---------- -------------------
13 0003312670000100001
6 0003292060000100001
6 0003266590001000001
6 0003284580000100001
...............


Even if you look at these records via util f/4/z501, you will find duplicate records.
One of these records has to be deleted from the database. But first you have to find out why duplicate z20 and z501 have been created.


02 z501_rec_key \
03 doc_number ..........000000630
03 sequence ............00001
03 claim_sequence ......00001
02 z501_claim_data \
03 alpha ...............L
03 claim_status ........
03 claim_date ..........20080303
03 claim_text ..........BATCH
03 vendor_reply ........
03 vendor_reply_date ...00000000
03 print_date ..........00000000
03 print_format ........


02 z501_rec_key \
03 doc_number ..........000000630
03 sequence ............00001
03 claim_sequence ......00001
02 z501_claim_data \
03 alpha ...............L
03 claim_status ........
03 claim_date ..........20080130
03 claim_text ..........BATCH
03 vendor_reply ........
03 vendor_reply_date ...00000000
03 print_date ..........00000000
03 print_format ........

Later:

Ex Libris removed all entirely duplicate records from Z501 and z20.

Site removed, through the GUI, all duplicate claims from Serials and Orders but the following:

Z501_REC_KEY
-------------------
delete from Z501 where Z501_REC_KEY = 0000139220001000001;
delete from Z501 where Z501_REC_KEY = 0000006300000100001;
delete from Z501 where Z501_REC_KEY = 0001841530001000001;
delete from Z501 where Z501_REC_KEY = 0003284660000100001;
delete from Z501 where Z501_REC_KEY = 0000182040001000001;
delete from Z501 where Z501_REC_KEY = 0003209890001000001;
delete from Z501 where Z501_REC_KEY = 0000498840001000001;
delete from Z501 where Z501_REC_KEY = 0000570040001000001;

8 rows selected.

abc50@ALEPH1> select Z20_REC_KEY from Z20 group by Z20_REC_KEY having count(*)>1;
**** Hit return to continue ****

Z20_REC_KEY
--------------------
00001956700052000001
delete from Z20 where Z20_REC_KEY = 00000154100182000001;
delete from Z20 where Z20_REC_KEY = 00000114400705000001;
delete from Z20 where Z20_REC_KEY = 00001962400003000001;
delete from Z20 where Z20_REC_KEY = 00000101400177000001;
delete from Z20 where Z20_REC_KEY = 00000154100181000001;
delete from Z20 where Z20_REC_KEY = 00000154100183000001;

We then used the UTIL A 17 18 to remove the above troublesome records.

The indexes for Z20 and Z501 have been sucessfully created/recreated via util A17 2/3/4

• Article last edited: 10/8/2013