non-integer otherIdNumber

[dustymc]

select guid from flat,coll_obj_other_id_num where flat.collection_object_id=coll_obj_other_id_num.collection_object_id and OTHER_ID_NUMBER!=round(OTHER_ID_NUMBER);

select substr(guid,1,instr(guid,':',1,2)-1) g, count(*) from (
  select guid from flat,coll_obj_other_id_num where flat.collection_object_id=coll_obj_other_id_num.collection_object_id and OTHER_ID_NUMBER!=round(OTHER_ID_NUMBER)
  3    ) group by substr(guid,1,instr(guid,':',1,2)-1) ;

G              COUNT(*)
-------------------- ----------
UAM:ES                5
CHAS:Bird            19
CHAS:Egg              8
UAM:Herb             51
MSB:Bird             11
UAMb:Herb             2
UNR:Mamm              1
UTEP:Herb           404
UTEP:Mamm             1
CHAS:Inv              2
UTEPObs:Herp             96
UAM:Mamm             11
UAM:Ento             42
DMNS:Mamm             1
UAM:Alg               6
UCM:Bird              1
UCM:Fish              2
UTEP:Herp            48
MSB:Para             45
USNPC:Para           87
UAM:Arc            6159
UCM:Egg               5
UTEP:HerpOS           4
DMNS:Bird            83
UWYMV:Mamm            1
MLZ:Bird            108
UCM:Herp             42
CHAS:Mamm            89
UTEP:ES            1619
CHAS:EH              16
MSB:Mamm            105
MSB:Herp             54
MVZ:Herp             12
MSB:Host             60
UMNH:Herp             1
DMNS:Egg             14
UTEP:Ento             1
UMZM:Mamm             1

These may be OK as NUMERIC? They're not expected to count, and there's no NEXT() functionality. If not they need transferred and the parser needs updated.

[dustymc]

How do I find the UWYMV specimen that has a non-integer other IDNumber? Do I enter the code you used into the write SQL function on Arctos?

Yes the first statement

select guid from flat,coll_obj_other_id_num where flat.collection_object_id=coll_obj_other_id_num.collection_object_id and OTHER_ID_NUMBER!=round(OTHER_ID_NUMBER)

should find them, BUT -

1) I'm not sure you can do anything about it, this is probably just my parser being weird, and

2) I'm not sure you need to do anything about it, I just noticed because they fell out of some migration tool.

Is there some reason to limit these to INTEGER, or is NUMBER OK?

[campmlc]

Not sure I have followed all this - did we decide that non-integer numbers are OK, not OK, OK in some circumstances, pros, cons? I understand that for catalog numbers, integers are highly recommended in order to allow Arctos to autoincrement the next number. Some documentation on this somewhere?

[dustymc]

We only "decided" by nobody objecting to what was discovered during the migration (essentially, Oracle has looser datatypes than PG).

I can't imagine why anyone would want to autoincrement an identifier. (And autoincremeinting is maybe in the top 10 reasons to use integer catalog numbers, but "otherwise people will fail in building citations" is my usual concern.)