|
|
Posted by mike on 09/25/36 11:59
Ok I finally got it. I still don't know why that didn't work. But
here is what I did instead and it worked.
On the join I changed it to the following.
On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)
mike wrote:
> 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]
|