On 26 Jun 2013 15:20, Kim Massar wrote:
I am using Scott's JDBCR programs to get data from our SQL server
into an RPG program. I have used this successfully before now I have
a problem with a null field in my WHERE statement. Is it possible to
compare for nulls? Is it possible to check for nulls in my
process_flag field below? I have tried '*Null' and 'is_null'
prepstm = JDBC_prepStmt(conn
: 'SELECT * ' +
'FROM dbo.Error_File ' +
'WHERE (dbo.Error_File.Process_flag = ?)'
);
If (prepstm = *NULL);
jdbc_close(conn);
return;
EndIf;
// Set SQL parameter values
JDBC_SetString(prepstm:1:'*NULL');
The equal test [i.e. using the = symbolic character] is one of the
/BASIC Predicates/ for which the result is /unknown/ when either operand
is the database NULL value. That is, the result of an equal test is not
only\always, either True or False.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzbaspred.htm
The database NULL value can be tested-for, only using a predicate for
which the result "cannot be unknown" [aka: the result must always be
determinate vs possibly-unknown]. Or, any NULL value as an operand must
have been coalesced to a specific non-NULL value before being tested
with any other predicate, thus knowing neither operand can be the NULL
value, and therefore the result similarly "cannot be unknown".
I must admit that for whatever reason my mind can not quickly
well-grasp, and thus I am often easily confused by what should be
expected for the effects of the /DISTINCT Predicate/ so I often avoid
it. Mostly, I do not want to review the docs to confirm the logical
equivalent predicates match what I want\think should happen.
Regardless, I think the following WHERE-clause would work well... for
what I *infer* is intended, as expressed in the OP:
prepstm = JDBC_prepStmt(conn
: ' SELECT *' +
' FROM dbo.Error_File as EF' +
' WHERE (EF.Process_flag is not distinct from' +
' cast(? as type-spec))'
);
And I believe that query will select the same as the following
predicates ORed, and that this logical equivalent effect can be
confirmed by the clarification in the docs [snippet included further below]:
WHERE ( Process_Flag IS NULL
or Process_Flag = ? )
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzdistinct.htm
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Language elements -> Predicates
_i DISTINCT predicate i_
"The DISTINCT predicate compares a value with another value.
>>--expression--IS--+-----+--DISTINCT FROM--expression---><
'-NOT-'
When the predicate is IS DISTINCT, the result of the predicate is true
if the comparison of the expressions evaluates to true. Otherwise, the
result of the predicate is false. The result cannot be unknown.
When the predicate IS NOT DISTINCT FROM, the result of the predicate is
true if the comparison of the expressions evaluates to true (null values
are considered equal to null values). Otherwise, the result of the
predicate is false. The result cannot be unknown.
The DISTINCT predicate:
value1 IS NOT DISTINCT FROM value2
is logically equivalent to the search condition:
( value1 IS NOT NULL AND value2 IS NOT NULL AND value1 = value2 )
OR
( value1 IS NULL AND value2 IS NULL )
..."
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzexists.htm
_i EXISTS predicate i_
"... The result cannot be unknown. ..."
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafznull.htm
_i NULL predicate i_
"... The result of a NULL predicate cannot be unknown. ..."
midrange.com
