when i compare a literal or constant integer value to a query
returning a NULL value, the results are not valid. In other words, if
i have TSQL statement as:
if 1 = (select returns NULL) then the next TSQL statement is executed
as if the conditional returned as TRUE, but 1 does not equal a NULL.
The results are different when i execute same TSQL on ASE125.
please let me know if you need more information.
Thank you,
Mark
ASE version: Adaptive Server Enterprise/15.0.3/EBF 16554 ESD#1/P/
RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/
----- RESULTS OF TSQL -----
USING constant[1]
1 !=[]yes
1 = []NO!!
1 = []NO!!
USING @l_int[1]
1 !=[]yes
1 !=[]yes
1 !=[]yes
----- COMPLETE TSQL -----
SET NOCOUNT ON
CREATE TABLE #singleint (anint int)
INSERT #singleint SELECT 1
-- sanity PRINTs
SELECT SUBSTRING(@@version,1,100)
SELECT anint "anint WHERE 1>0 " FROM #singleint WHERE 1>0
SELECT anint "anint WHERE 1=0 " FROM #singleint WHERE 1=0
SELECT anint "anint WHERE anint=0" FROM #singleint WHERE anint=0
PRINT "(SELECT anint FROM #singleint WHERE 1=0)"
IF (SELECT anint FROM #singleint WHERE 1=0) IS NULL PRINT "QUERY
ISNULL" ELSE PRINT "QUERY NOTNULL"
PRINT "(SELECT anint FROM #singleint WHERE anint=0)"
IF (SELECT anint FROM #singleint WHERE anint=0) IS NULL PRINT "QUERY
ISNULL" ELSE PRINT "QUERY NOTNULL"
DECLARE @l_int int
,@l_qry1 int
,@l_qry2 int
SELECT @l_int=1
SELECT @l_qry1=anint FROM #singleint WHERE 1=0
SELECT @l_qry2=anint FROM #singleint WHERE anint=0
-- begin tests
PRINT ""
PRINT "TEST: IS EQUAL -> SHOULD PRINT SIX 'it !='"
PRINT "USING constant[1]"
if 1 = NULL PRINT "1 = [%1!]NO!!",NULL else PRINT "1 !=
[%1!]yes",NULL
if 1 = (SELECT anint FROM #singleint WHERE 1=0) PRINT "1 =
[%1!]NO!!",@l_qry1 else PRINT "1 != [%1!]yes",@l_qry1
if 1 = (SELECT anint FROM #singleint WHERE anint=0) PRINT "1 =
[%1!]NO!!",@l_qry2 else PRINT "1 != [%1!]yes",@l_qry2
PRINT "USING @l_int[%1!]",@l_int
if @l_int = NULL PRINT "%1! =
[%1!]NO!!",@l_int,NULL else PRINT "%1! != [%2!]yes",@l_int,NULL
if @l_int = (SELECT anint FROM #singleint WHERE 1=0) PRINT "%1! =
[%2!]NO!!",@l_int,@l_qry1 else PRINT "%1! != [%2!]yes",@l_int,@l_qry1
if @l_int = (SELECT anint FROM #singleint WHERE anint=0) PRINT "%1! =
[%2!]NO!!",@l_int,@l_qry2 else PRINT "%1! != [%2!]yes",@l_int,@l_qry2
PRINT ""
PRINT "TEST: NOT EQUAL-> SHOULD PRINT SIX 'it !='",@l_int
PRINT "USING constant[1]"
if 1 != NULL PRINT "1 !=[%1!]yes",NULL else PRINT "1 =
[%1!]NO!!",NULL
if 1 != (SELECT anint FROM #singleint WHERE 1=0) PRINT "1 !
=[%1!]yes",@l_qry1 else PRINT "1 = [%1!]NO!!",@l_qry1
if 1 != (SELECT anint FROM #singleint WHERE anint=0) PRINT "1 !
=[%1!]yes",@l_qry2 else PRINT "1 = [%1!]NO!!",@l_qry2
PRINT "USING @l_int[%1!]",@l_int
if @l_int != NULL PRINT "%1! !=[%2!]yes",@l_int,NULL else
PRINT "%1! = [%2!]NO!!",@l_int,NULL
if @l_int != (SELECT anint FROM #singleint WHERE 1=0) PRINT "%1! !
=[%2!]yes",@l_int,@l_qry1 else PRINT "%1! = [%2!]NO!!",@l_int,@l_qry1
if @l_int != (SELECT anint FROM #singleint WHERE anint=0) PRINT "%1! !
=[%2!]yes",@l_int,@l_qry2 else PRINT "%1! = [%2!]NO!!",@l_int,@l_qry2