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

Description:
I have encountered a number of occurrences of obsolete material types in our system (see the 2 examples).
Is there an SQL command that could be run to see how many occurrences of non-valid material types there are in our data (material types other than those specified in XXX50/tab25.eng) and then change them to valid values?

Resolution:
You can use the following SQL to find non-valid material types:
SQL-xxx50> select z30_material, z30_rec_key from z30 minus select z30_material, z30_rec_key from z30 where z30_material in ('ISSUE', 'ISSBD');
After ISSBD you would include all of your other tab25 material types, separated by commas.
Assuming these results seem correct, you can use this SQL to change them:
SQL> update z30 set z30_material = 'yyyy' where z30_material = 'xxxxx';
The number of "Rows updated" should match the results for that z30_material type in the preceding "select".
You should, of course, back up the z30 table (using p_file_03) before performing any Update.
When updating z30 records with SQL one needs to consider whether the field being updated is one included (via expand) in BIB library indexes. Though the Material type is included in the LOC and PST expands (subfields "o" and "3"), it is not likely that it is actually being indexed. You can check your tab11_'s to be sure.