|
|
Posted by mike on 09/25/67 11:59
Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.
mike wrote:
> 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]
|