Nulls
How can I match on nulls? When I test for equality using the = operator between variables where both contain null, the test always fails.
Your test should fail. This is in accordance with the SQL standard, where null represents a variety of states – undetermined, invalid, and undefined among others. If both variables contain null, then the variables can never be equal since equality implies that we know enough about their respective states to determine that they are equal.
In order to test a variable for a null state in standard SQL DML, you might code:
select * from table where col is null
Equivalent Warehouse syntax, where $null is the Warehouse system constant:
read row = tag.table for col = $null
To re-iterate, the statement
read row = tag.table for col = var
will not return a row where both col and var contain null.
Note: in 2.07.3360, Warehouse introduced the == comparison operator. This operator greatly reduces the complexity of comparing nullable fields. See the answer to What is the == operator? in the Major new Warehouse Features section of this FAQ.
If I have an all-space field in a TurboIMAGE record, why does Oracle replaces the space in that field with a null when the record is inserted into an Oracle table?
I could live with this conversion, but later when I want to match records between TurboIMAGE and Oracle, an equal sign doesn’t give me what I expect.
These are two issues here: the properties of SQL nulls (nulls are NOT spaces) and the automatic conversions that take place on an Oracle insert/update via the OCI api. A solution has to take both issues into account.
Let’s assume we have two tables, a TurboIMAGE source and an Oracle target that have an identical logical structure of three columns: lastname, firstname, and middlename.
Let’s futher assume that when the Oracle tables is first loaded, all TurboIMAGE records contain a valid name in the lastname field but some TurboIMAGE fields contain spaces in either or both firstnameand middlename.
These spaces are converted by the Oracle OCI layer into null. Later, when we try to match the paired records on all three columns with the following script, the matches between spaces and nulls fail and our result set is smaller than we expect:
* read the TurboIMAGE file
read src = ima.emp
* match the Oracle row on all three values
read targ = ora.emp for src.lastname = lastname &
src.firstname = .firstname &
src.middlename = middlename
print ‘match on ‘, src.lastname
endread
endread
In order to correctly match these variable pairs, we need to check their contents once the row is read. In the following code example, the predicate checks are performed beneath the READ command for sake of document legibility.
* read the TurboIMAGE file
read src = ima.emp
* first match the Oracle row on lastname
read targ = for ora.lastname = lastname
* if the remaining paired columns are equal or if the TurboIMAGE column is space and
* its associated Oracle column is null, then we have a match
if ((src.firstname = ‘ ‘ and firstname = $null) or &
(src.firstname = firstname)) &
and &
((src.middlename = ‘ ‘ and middlename = $null) or &
(src.middlename = middlename))
print ‘match on ‘, src.lastname
else
print ‘no match on ‘, src.lastname
endif
endread
endread
What is the length of a CHAR or a VARCHAR2 variable when its contents are null?
When you use the LEN function to determine the length of a variable that contains null the result returned is $null. If you try to assign the result of the LEN function to a numeric receiving variable, that variable is unchanged. Finally, if you compare result of the LEN function to another numeric variable, the comparison will always fail as LEN does not return a number.
In order to establish whether a variable that accepts nulls has a valid length, you need to first check whether that variable contains null before invoking the LEN function.
1>
2> define c: oracle char(20) allow nulls
3>
4> * the variable clen is initialized to 8
5> define clen: numeric value 8
6>
7> * the LEN function returns $NULL
8> setvar c = $null
9> print “c=”; len(c)
10>
11> * clen is not set by the setvar operation since len(c) returns $null
12> setvar clen = len(c)
13> print “clen=”;clen
14>
15> * len(c) returns a non-numeric
16> if clen = len(c)
17> print ‘equals’
18> else if clen < len(c)
19> print ‘less than’
20> else if clen > len(c)
21> print ‘greater than’
22> else
23> print ‘none of the above’
24> endif
c=$NULL
clen= 8.000000
none of the above