Re: Nulls

Mark -

In an ORACLE system NULL essentially means 'not having a value', thus it appears that an explicit predecate test (e.g. IS (NOT) NULL) is required on a nullable column value when testing for values. However,

The standard practice is to use the NVL() standard function.

For example, say you want to test the nullable column 'col1' of varchar2(10) format for an inequality with the string 'Charlie' (equlities are no problem since col1 = 'x' when co1 is NULL returns FALSE anyway)

Without using NVL:
. . .
(AND col1 != 'Charlie'
 AND col1 IS NULL)

using NVL
. . .
AND NVL(col1,'*') != 'Charlie' -- which means that if col1 is null

                                it is decoded to the string '*' which != 'Charlie'

You run into indexing problems though if you have an index on col1, since using a function on a column switches off indexing capabilities on that column, so be careful.

To answer you're other specific questions

> I would appreciate advice on the best way of defing my tables.
Difficult to answer this. What sort of info. / advice are you looking for.  

As a rule, apply standard design practices e.g.

1. Normalise your database - aim for 3NF (3rd Normal Form), then degrade, dependant on application specific design issues;
2. Always define a PRIMARY KEY (unchanging, unique, necessarily NOT NULL) for EVERY table - the PK can be across more than one column of course;
3. Oracle v7 and above - build (table) constraints into the table definitions not (necessarily) into your apps.
4. Use stored procedures to implement business rules, rather than specific apps coding.

> Also how can I execute a query to find empty fields?
Easy!! (assuming that by 'empty' you mean columns which contain 'NULL' values?)

e.g.

SELECT *
FROM tab1,tab2
WHERE tab1.x = tab2.x
AND tab1.col2 IS NULL -- only select rows where col2 is null

>
  Received on Wed Oct 01 1997 - 00:00:00 CDT