Schultz’s PowerBuilder Notes

Sybase and Nulls


If you have not read about how PowerBuilder treats nulls, you may want to read  nulls first.

Unlike PowerBuilder, Sybase SQL treats variables set to null just like null is a particular value.  In other words, Sybase SQL resolves 2 different variables, both set to null, as being equal to each other.  In the PB script, li_null = li_null is neither TRUE or FALSE, it is null.  But in Sybase SQL, @li_null = @li_null is TRUE.

declare @li_null int,
@li_null2 int

select @li_null = null
select @li_null2 = null
select @li_null
select @li_null2

-- Returns 1
-- variable with null value = itself
select 1
where @li_null = @li_null

-- Returns 2
-- variable with null value = another variable = null
select 2
where @li_null = @li_null2

-- No rows returned
select 3
where @li_null <> @li_null

-- No rows returned
select 4
where @li_null <> @li_null2

Things get trickier when dealing with tables with null columns.  A column with a value of null works like PowerBuilder.  That is, if #TEMP_TABLE contains one row and its KEY column is null, #TEMP_TABLE.KEY = #TEMP_TABLE.KEY resolves to null, not TRUE.

But if you compare the same column to a literal null or a variable with the value of null, it works like Sybase SQL variable.  #TEMP_TABLE.KEY = null resolves to TRUE.

--  Fun with nulls and joins
create table #temp1
(key1 int null,
data1 char(1))

Insert into #temp1 (key1, data1) Values (null, "A")
Insert into #temp1 (key1, data1) Values (2, "B")
Insert into #temp1 (key1, data1) Values (3, "C")

create table #temp2
(key2 int null,
data2 char(1))

Insert into #temp2 (key2, data2) Values (null, "X")
Insert into #temp2 (key2, data2) Values (2, "Y")
Insert into #temp2 (key2, data2) Values (999, "Z")

select * from #temp1
select * from #temp2

-- limit retrieve to one row
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"

-- -- one row retrieved
-- select * from #temp1, #temp2
-- where data1 = "A"
-- and   data2 = "X"
-- and   data2 = data2

-- no rows retrieved
-- null column does not join to null column
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   key1  = key2   -- join

-- no rows retrieved
-- null column excluded if it tries to = ietself
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   key1  = key1

-- 1 rows retrieved
-- not null null column does not excluded its row,  if it tries to = ietself
select * from #temp1, #temp2
where data1 = "B"
and   data2 = "Y"
and   key1  = key1

-- retrieves 1 row
-- null column =  variable with null value
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   key1  = @li_null

-- returns 1 row
-- null column = null literal
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   data2 = data2
and   key1  = null

-- returns no row
-- null column = itself (same column)
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   key1  = key1

-- returns no row
-- join on null to same table
select * from #temp1 t1a, #temp1 t1b
where t1a.data1 = "A"
and   t1b.data1 = "A"
and   t1a.key1  = t1b.key1

drop table  #temp1
drop table  #temp2
About these ads

January 7, 2009 - Posted by | Database | , , , ,

1 Comment »

  1. First blog I read after wakeup from sleep today!

    —————————-
    Search fast!

    Comment by Nyachuom | March 3, 2009 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: