We help IT Professionals succeed at work.

SQL statement fails to retrieve results in Oracle

Medium Priority
573 Views
Last Modified: 2013-11-16
Can someone please explain to me why in Oracle the statement
SELECT DISTINCT EMH_FLD1 FROM EMPHIST WHERE EMH_FLD1<>'' AND EMH_FLD1 IS NOT NULL ORDER BY EMH_FLD1
returns no rows but the statement
SELECT DISTINCT EMH_FLD1 FROM EMPHIST WHERE EMH_FLD1<>' ' AND EMH_FLD1 IS NOT NULL ORDER BY EMH_FLD1

If ifnd this extremely annoying.  Doing the same thing in SQL Server of course works fine.
I know I can work around it by simply putting the space between the apostrophes, but that is not my point.  Is it a server side setting on Oracle.  Is it simply because I used the NULL clause on the CREATE TABLE?  What is the best way to create then so that I don't have these problems.  I want to present the user a list of possible values stored in the table and I do not want to include rows with a NULL or EMPTY and I am writing an application that supports several backends such as Oracle, SQL Server and Sybase.  I also know that the client may be entering data directly into my tables through other processes so I cannot *always* control the way the data is stored, so I want a catchall that is going to work 100% of the time.

Here is the SQL to set up the test.
CREATE TABLE EMPHIST (EMH_KEY NUMERIC(11) NOT NULL ,
      EMH_FLD1 VARCHAR (20) NULL ,
PRIMARY KEY (EMH_KEY));

INSERT INTO EMPHIST VALUES (1,NULL);
INSERT INTO EMPHIST VALUES (2,'');
INSERT INTO EMPHIST VALUES (3,'SOMEVALUE');
COMMIT;

SELECT DISTINCT EMH_FLD1 FROM EMPHIST WHERE EMH_FLD1<>'' ORDER BY EMH_FLD1

SELECT DISTINCT EMH_FLD1 FROM EMPHIST WHERE EMH_FLD1<>' ' ORDER BY EMH_FLD1
Comment
Watch Question

Commented:
'' = NULL because it contains nothing, nothing means NULL or vice versa

' ' <> NULL because it contains a space, space is a special character

following is the statement which will work for you.
SELECT DISTINCT EMH_FLD1 
FROM EMPHIST 
WHERE EMH_FLD1 <> NULL 
ORDER BY EMH_FLD1

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
<<  I do not want to include rows with a NULL or EMPTY >>

If you want to achieve this, then while creating the table default that particular column to be of some value like

EMH_FLD1 VARCHAR (20) NOT NULL DEFAULT 'X' -- where x is any significant value for that column.

This would prevent you from all those cases which you mentioned earlier and will help you out for all databases like Oracle, SQL Server and Sybase as you require.
The only solution is to check for it with the following SQL.  Putting a default value such as an X will only pollute the data since it is a free form text column and that would be a vlaue I do not want in the data.
SELECT DISTINCT EMH_FLD1 FROM EMPHIST WHERE EMH_FLD1<>' ' AND EMH_FLD1 IS NOT NULL ORDER BY EMH_FLD1

I guess it is related to the age old problem of NULL and NOT NULL which I'm sure serves a purpose for someone, but I find anoying.  
Thanks to all for lending their assistance.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.