CODEWITHSUNDEEP
sqloracle-databaseplsql
Dennis
Dennis

Reputation: 15

PL/SQL Oracle condition equals

I think I'm encountering a fairly simple problem in PL/SQL on an Oracle Database(10g) and I'm hoping one of you guys can help me out.

I'm trying to explain this as clear as possible, but it's hard for me. When I try to compare varchar2 values of 2 different tables to check if I need to create a new record or I can re-use the ID of the existing one, the DB (or I) compares these values in a wrong way. All is fine when both the field contain a value, this results in 'a' = 'a' which it understands. But when both fields are NULL (or '' which Oracle will turn into NULL) it can not compare the fields.

I found a 'solution' to this problem but I'm certain there is a better way.

rowTable1  ROWTABLE1%ROWTYPE;
iReUsableID INT;

SELECT * INTO rowTable1
FROM TABLE1
WHERE TABLE1ID = 'someID';

SELECT TABLE2ID INTO iReUsableID
FROM TABLE2
WHERE NVL(SOMEFIELDNAME,' ') = NVL(rowTable1.SOMEFIELDNAME,' ');

So NVL changes the null value to ' ' after which it will compare in the right way.

Thanks in advance,

Dennis

Upvotes: 0

Views: 881

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your method is fine, unless one of the values could be a space. The "standard" way of doing the comparison is to explicitly compare to NULL:

WHERE col1 = col2 or col1 is null and col2 is null

In Oracle, comparisons on strings are encumbered by the fact that Oracle treats the empty string as NULL. This is a peculiarity of Oracle and not a problem in other databases.

Upvotes: 0

Andrej
Andrej

Reputation: 118

This is indeed a simple and usable way to compare nulls. You cannot compare NULLS directly since NULL is not equal NULL. You must provide your own logic who you would like to compare, what you've done with NVL(). Take in mind, you are treating NULLS as space, so ' ' in one table would be equal to NULL in another table in your case.

There are some other ways (e.g. LNNVL ) but they are not some kind of a "better" way, I think.

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

In Oracle (or any RDBMS I believe), one NULL is not equal to another NULL. Therefore, you need to use the workaround that you have stated if you want to force 2 NULL values to be considered the same. Additionally, you might want to default NULL values to '' (empty) rather than ' ' (space).

From Wikipedia (originally the ISO spec, but I couldn't access it):

Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.

As mentioned by Jan Spurny, you can use LNNVL for comparison. However, it would be wrong to say that a comparison is actually being made when both values being compared are NULL.

Upvotes: 0

Jan Spurny
Jan Spurny

Reputation: 5528

You can use LNNVL function (http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions078.htm) and reverse the condition:

SELECT TABLE2ID INTO iReUsableID
FROM TABLE2
WHERE LNNVL(SOMEFIELDNAME != rowTable1.SOMEFIELDNAME);

Upvotes: 2

Related Questions