Oops, I should have looked at the current function list before I replied.
Troy -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of David M. Blocker Sent: Thursday, February 24, 2005 8:52 AM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: EQNull Troy We DO have that! IFNULL(arg1,arg2,arg3) = If Arg1 is NULL value = arg2, else = arg3! Select * from myMainTable t1, myStatusLookupTable t2 where ifnull(t1.StatusSetting, 'X',t1.StatusSetting) = t2.StatusSetting David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Troy Sosamon" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Wednesday, February 23, 2005 6:02 PM Subject: [RBG7-L] - Re: EQNull > 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 > >
