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

Description:
Should converted Znn records have nulls or blanks in empty fields?

SQL Server considers a blank string ("") to be different from a NULL value, so a character column can be defined as NOT NULL and yet still include blank strings in the data.

If we have spaces in the Z fields of the Oracle table, SQL will consider them as characters. Will empty data display on the reports, etc., as a result?

Resolution:
NOT NULL" is for a required field. Aleph is *not* going to let you put blanks in a required field.

For example, the SQL "desc" for z30 shows this:

Z30_REC_KEY NOT NULL CHAR(15)

Though SQL will let you set the z30_rec_key to blanks, Aleph does not.

The issue of null vs blanks comes into play in fields which are defined as CHAR but are not required.

KB 16384-8306 discusses a case where Aleph/Oracle is changing null to blanks. But the SQLs below show that *most* CHAR fields are being initialized to NULL ... and kept as NULL.

I spaced over the "GEN" in the z30_collection for usm50 z30 000003767 in Cataloging and saved the record. I find that it was saved as NULL *not* as blanks.

I believe that the normal thing is for empty Aleph CHAR fields to be set to NULL and I think that this is how converted data should be treated.

The only exception (based on KB 16384-8306) might be the z30_item_process_status but -- as far as Aleph is concerned -- null will work OK there too.


usm50@ALEPH201> select count(*) from z30 where z30_copy_id is null;
**** Hit return to continue ****

COUNT(*)
----------
141937


usm50@ALEPH201> select count(*) from z30 where z30_copy_id like ' %';
**** Hit return to continue ****

COUNT(*)
----------
0


usm50@ALEPH201> select count(*) from z30 where z30_pages is null;
**** Hit return to continue ****

COUNT(*)
----------
148445


usm50@ALEPH201> select count(*) from z30 where z30_pages like ' %';
**** Hit return to continue ****

COUNT(*)
----------
0


usm50@ALEPH201> select count(*) from z30 where z30_collection is null;
**** Hit return to continue ****

COUNT(*)
----------
10783


usm50@ALEPH201> select count(*) from z30 where z30_collection like ' %';
**** Hit return to continue ****

COUNT(*)
----------
0

• Article last edited: 10/8/2013