|
|
Posted by mike on 11/28/03 11:59
Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.
Ed Murphy wrote:
> mike wrote:
>
> > I have a new table that I am adding to a script that I wrote. This
> > table has 3 fields, the first 2 fields are used in the on statement as
> > being = other fields in the script.
> >
> > The first field always has data in it, but the 2nd field is sometimes
> > null.
> >
> > So my problem is if both fields have data in them and they both match
> > to the data in the fields that I am linking them to, then it returns
> > the 3rd field without a problem. However if the 2nd field is null then
> > it is returning a null for the 3rd field. I have checked and the field
> > that I am linking to is null also.
> >
> > So if I have
> >
> > select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
> > from table1 t1
> > join table2 t2
> > on t1.field1=t2.field1 and t1.field2=t2.field2
> >
> > with 2 records in each table
> > table1: record1: data, data
> > record2: data, null
> > table2: record1: data,data,data
> > record2: data,null,data
> >
> > what I get from the script is
> > record1: data, data,data,data,data
> > record2: data,null,data,null,null
> >
> >
> > I would expect
> > record2: data,null,data,null,data
>
> Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
> is much more confusing.
>
> Run some sanity checks on the data:
>
> select * from table1 where field2 is null
> select * from table1 where field2 = 'NULL'
>
> select * from table2 where field2 is null
> select * from table2 where field2 = 'NULL'
>
> What tool are you using to pull the data? Some clients (e.g. Crystal
> Reports) have a "convert nulls to empty values" option.
Navigation:
[Reply to this message]
|