Sometimes I have seen performance problems with eqnull set on. Wouldn't it be nice if there were a function where you could specify a value to use when something is null - isnull(fieldname, valuewhennull). In this case it might be isnull(StatusSetting, 'X').
Do your lookup or join like: Select * from myMainTable t1, myStatusLookupTable t2 where isnull(t1.StatusSetting, 'X') = t2.StatusSetting myStatusLookupTable would need the row with 'X' instead of null. Troy -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Fleming Sent: Wednesday, February 23, 2005 3:19 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: EQNull David, I agree with you, but... I have a status setting (TEXT 1) in scheduled work which is initially blank which means there is nothing preventing it from being done. In a lookup table a blank equates to "Ready", and all other single character codes mean it's waiting for something (parts, labor, approval, etc.). If I set EQNULL ON, then the lookup is true and it works fine (null = null). I have used converting to 7.1 from 6.5 as an opportunity to rewrite a lot of "suspect" code, but this is one area I haven't. I don't see a problem where two null values are compared equal. I was just curious if others have run into a problem with EQNULL ON. Thanks, Dennis ***** At 04:56 PM 2/23/2005 -0500, you wrote: >Dennis > >On principal, I just don't mess with this setting. NULL should NOT be >treated as equal to NULL in my mind: X = Y is not the same as X IS NULL. > > >David Blocker >[EMAIL PROTECTED] >781-784-1919 >Fax: 781-784-1860 >Cell: 339-206-0261 >----- Original Message ----- >From: "Dennis Fleming" <[EMAIL PROTECTED]> >To: "RBG7-L Mailing List" <[email protected]> >Sent: Wednesday, February 23, 2005 2:00 PM >Subject: [RBG7-L] - EQNull > > > > I realize that we (I) tend to recycle some prior discussions. > > Dr R's FTE article is certainly helpful in Equal Null comparisons, but... > > > > There was a problem in an earlier 7.1 version where a blank is saved as a > > Null and this has corrected in most instances. (It still reverts to a null > > when selecting a blank in a screen form popup menu.) > > > > By setting EQNULL ON has been a good workaround for me, but my question >is, > > are there any drawbacks of leaving EQNULL on? > > > > TIA, > > > > Dennis > > ***** > > Dennis Fleming > > IISCO > > http://www.TheBestCMMS.com > > Phone: 570 775-7593 > > Mobile: 570 351-5290 > > > > Dennis Fleming IISCO http://www.TheBestCMMS.com Phone: 570 775-7593 Mobile: 570 351-5290
