Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

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

    • 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