Re: Easy SQL problem

And just to add:
When creating a table that can have vacant fields ( they can be NULL, which is the default) you can avoid the empty string vs NULL issue by setting a default value for Varchar2 and Char fields to "" ( empty string) and Numeric ones to 0.

Just a thought...

"Alan" <alanshein_at_erols.com> wrote:

>
>Also, just for clarification, a null (unknown value) is not the same thing
>as an empty string (""), at least in Oracle. Most new users are confused by
>this. It relates to there being three states for a check box; Checked,
>Unchecked, and Never Checked. Unchecked is equal to an empty string, and
>Never Checked is NULL (depending, of course, on the prgramming environment
>and tool).
>
>"Richard Foote" <Richard.Foote_at_oracle.com> wrote in message
>news:3D5327ED.AC92D4DF_at_oracle.com...
>> Hi Ben,
>>
>> The nice thing about an easy SQL problem is that generally it's an easy
>> SQL solution.
>>
>> What you are highlighting is not a 9i feature but a characteristic of
>> how Oracle deals with nulls since time began.
>>
>> Logically, the best way to view a null is as an unknown. Therefore if
>> you ask if one unknown value is equal to (or not equal to) another
>> unknown value the answer of course is you don't know.
>>
>> Therefore a condition variable = null can never be treated as true and
>> will never return a row.
>>
>> Neither will variable <> null.
>>
>> Therefore, if you want to know if a column is (or is not) null you must
>> use the 'is null' (or 'is not null') notation.
>>
>> Cheers
>>
>> Richard
>> Ben wrote:
>> >
>> > Any one know if this is a new 9i feature or not?
>> >
>> > I have the following table.
>> >
>> > SQL> desc table1
>> > Name Null? Type
>> > ----------------------------------------------------- --------
>> > -----------
>> > ID
>> > VARCHAR2(5)
>> > NAME
>> > VARCHAR2(5)
>> >
>> > SQL> insert into table1 values ('242','');
>> >
>> > 1 row created.
>> >
>> > SQL> select * from table1;
>> >
>> > ID NAME
>> > ----- ------------------------------
>> > 1 A
>> > 2 A
>> > 3 C
>> > 4 D
>> > 5. E
>> > aaa C
>> > 242
>> >
>> > 7 rows selected.
>> >
>> > SQL> select * from table1 where name is null;
>> >
>> > ID NAME
>> > ----- ------------------------------
>> > 242
>> >
>> > SQL> select * from table1 where name is not null;
>> >
>> > ID NAME
>> > ----- ------------------------------
>> > 1 A
>> > 2 A
>> > 3 C
>> > 4 D
>> > 5. E
>> > aaa C
>> >
>> > 6 rows selected.
>> >
>> > SQL> select * from table1 where name <> '';
>> >
>> > no rows selected
>> >
>> > SQL> select * from table1 where name = '';
>> >
>> > no rows selected
>> >
>> > My question is why do I get no rows selected for "select * from table1
>> > where name <> '';" and "select * from table1 where name = '';"?
>> >
>> > Any idea?
>> >
>> > Thanks!!
>> > Ben
>

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Fri Aug 09 2002 - 08:48:24 CDT