Re: Nulls

This treatment of nulls is then the same as I am used to. However, what I was trying to get around was the effects that this has on the use of forms.

Say I am using a table form and move to a new record, I only want to fill the required fields but I want the non required fields to be consistent. ie a user should not get different results when looking for null or blank fields.

As I am not writing these queries explicitly I don't get the oppurtunity to add nvl functions.

The reference I made about table design was just relating to the definition of non-required fields.
Would they just be left as field & type or can NOT NULL & DEFAULT clauses be used to good effect without causing problems for the forms.

The searching for null values query also related to using the form query.

Martyn <news_at_cavett.globalnet.co.uk> wrote in article <01bccea8$71e82c80$LocalHost_at_cavett.globalnet.co.uk>...
> 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