Skip to Content
0
Former Member
Apr 21, 2015 at 04:47 PM

Why does ASE treats not exists value as null.

14 Views

Here is a repro that ASE 15.0.3 treats a non existant value as null value.

Looks like the case statement is applied after result set being created. Is this a normal behavier on outer joins?

create table table1 ( id int, b int )

go

create table table2 ( id int, c int null )

go

insert table1 values ( 1, 1)

go

insert table1 values ( 2, 2)

go

insert table1 values ( 3, 3)

go

insert table1 values ( 4, 4)

go

insert table2 values ( 1, 0)

go

insert table2 values ( 2, 1)

go

insert table2 values ( 3, null)

go

select x.id, x.b, case

when y.c is null then 'Created'

when y.c = 0 then 'Inspected'

when y.c = 1 then 'Approved'

when y.c = 2 then 'Deleted'

else 'Not exists'

end as Status

from table1 x left join table2 y

on x.id = y.id

go

select x.id, y.id, x.b, case

when y.c is null then 'Created'

when y.c = 0 then 'Inspected'

when y.c = 1 then 'Approved'

when y.c = 2 then 'Deleted'

else 'Not exists'

end as Status

from table1 x left join table2 y

on x.id = y.id

go

Thanks,

Sid.