Home » SQL & PL/SQL » SQL & PL/SQL » sql select where clause not working
sql select where clause not working [message #270692] Thu, 27 September 2007 15:21 Go to next message
choinierep
Messages: 3
Registered: September 2007
Junior Member
Hi, I use MS SQL and Firebird a lot and just started using oracle and ran into some puzzling behavior. Ex:

select CITY
from CUSTOMERS

returns 35 rows with no nulls and no empty strings.

select CITY
from CUSTOMERS
where CITY <> ''

returns no row. Why? It works under firebird and MS SQL. I figured it would work in Oracle also.

Re: sql select where clause not working [message #270693 is a reply to message #270692] Thu, 27 September 2007 15:27 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess you are looking for IS [NOT] NULL. Something like
SELECT city
FROM customers
WHERE city IS NOT NULL;

Although previous experience helps, sometimes it is necessary to consult the Documentation. I suggest you to read the SQL Reference book.
Re: sql select where clause not working [message #270694 is a reply to message #270692] Thu, 27 September 2007 15:28 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Because in oracle, an empty string is the same thing as a null. And null is never equal, or not equal, to anything - it is null.

MYDBA@orcl > create table t(id number, data number);

Table created.

MYDBA@orcl > insert into t values (1, null);

1 row created.

MYDBA@orcl > select * from t where data is null;

        ID       DATA
---------- ----------
         1

1 row selected.

MYDBA@orcl > select * from t where data is not null;

no rows selected

MYDBA@orcl > select * from t where data = null;

no rows selected

MYDBA@orcl > select * from t where data = ''
  2  ;

no rows selected

MYDBA@orcl > select nvl('','Empty string is null') from dual;

NVL('','EMPTYSTRINGI
--------------------
Empty string is null

1 row selected.

MYDBA@orcl >

Re: sql select where clause not working [message #270701 is a reply to message #270694] Thu, 27 September 2007 16:01 Go to previous messageGo to next message
choinierep
Messages: 3
Registered: September 2007
Junior Member
Thanks, thats the info I was looking for. In Firebird and MSSQL a varchar/Char can store NULL or Empty String and it looks the same to the end user.
Re: sql select where clause not working [message #270710 is a reply to message #270701] Thu, 27 September 2007 16:51 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
choinierep wrote on Thu, 27 September 2007 22:01
In Firebird and MSSQL a varchar/Char can store NULL or Empty String and it looks the same to the end user.

Nice feature Wink

At this point we normally get a furious tirade about how two visually indistinguishable kinds of emptiness are absolutely mission-critical for character information but not for some reason dates.

Re: sql select where clause not working [message #270727 is a reply to message #270710] Thu, 27 September 2007 21:06 Go to previous messageGo to next message
choinierep
Messages: 3
Registered: September 2007
Junior Member
From what I understand Firebird and MS SQL are following that standard and Oracle is not. Given the following SQL statement in Oracle:

SELECT city
FROM customers
WHERE city IS NOT NULL

In firebird and MS SQL I would have to do

SELECT city
FROM customers
WHERE city IS NOT NULL and city <> ''

The reason as I understand is so you know if that field was ever filled out. Ex: if city was never filled in then it would be NULL. If city was fill in and then blanked out (user backspaced the city field in the user interface) then it would be empty string. There are merits to both systems. But personally I like Oracles way of doing thing better, but I'm stuck with using all three SQL DB.

[Updated on: Thu, 27 September 2007 21:08]

Report message to a moderator

Re: sql select where clause not working [message #270788 is a reply to message #270727] Fri, 28 September 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
From what I understand Firebird and MS SQL are following that standard and Oracle is not

Which standard?

Regards
Michel
Re: sql select where clause not working [message #270803 is a reply to message #270727] Fri, 28 September 2007 01:19 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
choinierep wrote on Fri, 28 September 2007 03:06
There are merits to both systems.

That's very diplomatic of you Smile

For some reason if an end user of the same application enters a date value and then blanks it out, nobody seems to mind that it goes back to being null again and not some special "there was a value here but I took it out" value that looks the same as null but isn't.

People then usually go on to give the example of middle names such as my wife's, which is apparently '' and has length zero according to empty-stringists, presumably giving her three names with an average length of 5, and not two names with an average length of 7.5 as she might have thought.
Re: sql select where clause not working [message #270811 is a reply to message #270788] Fri, 28 September 2007 01:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Fri, 28 September 2007 07:50
Quote:
From what I understand Firebird and MS SQL are following that standard and Oracle is not

Which standard?

Regards
Michel


The ANSI SQL standard if I'm not mistaken.

MHE

[Updated on: Fri, 28 September 2007 01:30]

Report message to a moderator

Re: sql select where clause not working [message #270820 is a reply to message #270811] Fri, 28 September 2007 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ANSI standard is not one.
It is like an onion field with multiple fruits and layers in each one.
When we talk about ANSI standard, we have to say which one (86,87,89,92,2,3,1999,2003,2006) and which level.
In addition they are FIPS add-ons (FIPS127-1,-2, ISO 9075/xxx...) about SQL that describe each feature for each level, or more or less.
"standard" is meaningless in SQL or there is a "standard" for each vendor and each vendor is standard... at the entry level.

Regards
Michel
Re: sql select where clause not working [message #270821 is a reply to message #270820] Fri, 28 September 2007 01:46 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
In one of the recent debates on this subject, somebody on cdos finally quoted the ANSI standard as saying that a string can have zero or more characters, as if that justified the whole two-kinds-of-emptiness business, gave my wife an extra name, crippled IS NULL and so on. I'm afraid I remain unconvinced that ANSI has anything helpful to say on the subject.
Re: sql select where clause not working [message #270855 is a reply to message #270821] Fri, 28 September 2007 03:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel, I know that ANSI is not ONE standard. But put some effort in it, if you will. Wink
If you look around a bit you'd find that both ANSI SQL 92 and ANSI SQL 99 stipulate that
1. a string can have zero or more characters
2. NULL is not the same as a zero-length string

ISO SQL 2003 (a draft is available on the net) is a revision of the ISO/ANSI SQL 99 standard but I don't think that the treatment of NULLs is different in there.

Would it be a good thing if Oracle were to change its behavior on this regard? William Robertson is right, I'm afraid. It would certainly cause a lot of headaches. Oracle better sticks with its own rules.

But that's entirely off topic.

MHE
Re: sql select where clause not working [message #270860 is a reply to message #270855] Fri, 28 September 2007 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I did it and indeed Oracle only partially supports E021-02 (CHARACTER VARYING data type) and E021-03 (character literals) subfeatures of Core SQL:2003 in that it does not distinguish a zero-length VARCHAR string from NULL and regards the zero-length literal '' as being null.

Regards
Michel
Re: sql select where clause not working [message #270863 is a reply to message #270860] Fri, 28 September 2007 03:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I guess this is one of those things that will remain food for thought forever...

MHE
Re: sql select where clause not working [message #270931 is a reply to message #270692] Fri, 28 September 2007 07:52 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
If I remember correctly, in fairness to Oracle, they implemented the current method in use today well before the SQL 92 standard existed. And changing something like that after the fact, for backwards compatibility reasons, is really not practical.

This thread, however, is yet another example of why applications will have a tough time of being "database vender independent". Core details are implemented differently.

I of course "like" the zero length string being null. But of course I'm biassed. But I've never seen a situation in which the other way was truly needed.
Previous Topic: package chk_pack
Next Topic: how can I user trigger to implement Referential Integrity in distributed database.
Goto Forum:
  


Current Time: Wed Dec 07 18:28:23 CST 2016

Total time taken to generate the page: 0.06134 seconds