[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

SQL statement fails to retrieve results in Oracle

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
0
hmstechsupport
Asked:
hmstechsupport
3 Solutions
 
sonicefuCommented:
'' = 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

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<<  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.
0
 
hmstechsupportAuthor Commented:
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now