NULL situation
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 blankvalue ?
alter table modify fullname char(32) default '', not null; -- will this changenulls already in table or cause an error?
John
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 25Near 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 ablank value ?
alter table modify fullname char(32) default '', not null; -- will thischange 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/
Adding a DEFAULT clause will not affect existing rows at all. You will not
be able to include a NOT NULL clause though it will fail with an error. You
COULD create a NOT NULL constraint set to FILTERING, but that will just
create rows in the exceptions tables that you will have to process to update
or delete the offending rows, which is just as easy to do without the
constraint. Just run and UPDATE ... SET <col> = <default> WHERE <col> IS
NULL; after adding the default clause to the column.
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Feb 8, 2010 at 4:30 PM, John Adamski <adamski@graceland.edu> wrote:
> 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.
>
>
--0015174be8eae757d3047f1f1017