IDS Forum

RE: NULL situation

Posted By: Jarrod Teale
Date: Monday, 8 February 2010, at 4:52 p.m.

In Response To: NULL situation (John Adamski)

The second command will fail - you can't add a not null clause to a
column that contains NULLs.

The first one may work - depends on how the NULL gets into the column.
If the application is saying "INSERT INTO (fullname) VALUES (NULL)" then
the default won't help - it will take the NULL and store it.
If the application is simply omitting the column from the insert /
update, then the default clause will apply

Take the test script below:

create table testnulls (

rownum serial,

fullname char(32)

);

insert into testnulls values (0, "Jarrod");
insert into testnulls values (0, "Jarrod2");
insert into testnulls values (0, NULL);
insert into testnulls values (0, NULL);
insert into testnulls values (0, "Jarrod3");

-- Add Default clause
alter table testnulls modify fullname char(32) default '';
select rownum, "STUFF" || NVL(TRIM(fullname), "-NULLHERE-") || "STUFF"
from testnulls;

-- Explicitly add NULL
insert into testnulls values (0, NULL);
select rownum, "STUFF" || NVL(TRIM(fullname), "-NULLHERE-") || "STUFF"
from testnulls;

-- Add row with no defined name
insert into testnulls (rownum) values (0);
select rownum, "STUFF" || NVL(TRIM(fullname), "-NULLHERE-") || "STUFF"
from testnulls;

-- Try to add the not null clause - fails.
alter table testnulls modify fullname char(32) default '' NOT NULL;
select rownum, "STUFF" || NVL(TRIM(fullname), "-NULLHERE-") || "STUFF"
from testnulls;

AND THE OUTPUT below here

[informix@jarrod(10.10.70.133) ~]$ dbaccess jarrod test.sql

Database selected.

Table created.

1 row(s) inserted.

1 row(s) inserted.

1 row(s) inserted.

1 row(s) inserted.

1 row(s) inserted.

Table altered.

rownum (expression)

1 STUFFJarrodSTUFF

2 STUFFJarrod2STUFF

3 STUFF-NULLHERE-STUFF

4 STUFF-NULLHERE-STUFF

5 STUFFJarrod3STUFF

5 row(s) retrieved.

1 row(s) inserted.

rownum (expression)

1 STUFFJarrodSTUFF

2 STUFFJarrod2STUFF

3 STUFF-NULLHERE-STUFF

4 STUFF-NULLHERE-STUFF

5 STUFFJarrod3STUFF

6 STUFF-NULLHERE-STUFF

6 row(s) retrieved.

1 row(s) inserted.

rownum (expression)

1 STUFFJarrodSTUFF

2 STUFFJarrod2STUFF

3 STUFF-NULLHERE-STUFF

4 STUFF-NULLHERE-STUFF

5 STUFFJarrod3STUFF

6 STUFF-NULLHERE-STUFF

7 STUFFSTUFF

7 row(s) retrieved.

530: Check constraint () failed.
Error in line 25
Near character position 1

rownum (expression)

1 STUFFJarrodSTUFF

2 STUFFJarrod2STUFF

3 STUFF-NULLHERE-STUFF

4 STUFF-NULLHERE-STUFF

5 STUFFJarrod3STUFF

6 STUFF-NULLHERE-STUFF

7 STUFFSTUFF

7 row(s) retrieved.

Database closed.

Jarrod Teale
Team Lead - Manufacturing Execution Systems
Automation & Process Control Group
NZ Technical
Fonterra
email: jarrod.teale@fonterra.com

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
John Adamski
Sent: Tuesday, 9 February 2010 10:31 a.m.
To: ids@iiug.org
Subject: NULL situation [18944]

HPUX 11.23 IDS 10.00.FC9

We are trying to find the best solution for a situation that our
third-party software and their database design has created. Since we do
not have full control of the third-party software nor their database
design we need a way to clean up the mess left behind.

The situation is this: we get null values where the software is not
expecting nulls or is not coded to handle nulls might be more accurate.
Most of the null values come for the software itself. Example might be
program A creates a row in an accounting table and program Q down the
road then tries to do something with that row after a number of other
programs have manipulated the data, but it fails cause a field has a
null. Or worse yet is processes the row incorrectly because it did not
have coding to handle nulls.

Since we have a bit more flexibility in changing the tables in the
system rather than the actual software, the tentative plan is to modify
the tables to have default values on the fields we know causes us
problems.

A question came up that I can't remember back to my training 8 years ago
so want to pose it here. If we add a default to a field already in the
table will it apply that default to null records or will it just apply
the default to new records? My memory say as long as not null clause is
not added it will leave current rows alone.

Example if we have a NAMES table that has a field called fullname that
is a
char(32) with no default set an also doesn't have the not null clause -
just a plain char(32). And now I alter the table to have a default of
blank (''), my assumption is since null are still allowed it would leave
the nulls already in the table alone. I would have to add the not null
clause, which seems like a catch 22 as wouldn't I get an error cause I
have nulls? I need a memory refresh.

alter table modify fullname char(32) default ''; -- only new rows get a
blank value ?

alter table modify fullname char(32) default '', not null; -- will this
change nulls already in table or cause an error?

John

************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.

DISCLAIMER:
This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email.
You may not use, disclose or copy this email or its attachments in any way.
Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/

Messages In This Thread

• NULL situation
  John Adamski -- Monday, 8 February 2010, at 4:30 p.m.
  • RE: NULL situation
    Jarrod Teale -- Monday, 8 February 2010, at 4:52 p.m.
  • Re: NULL situation
    Art Kagel -- Monday, 8 February 2010, at 6:21 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.