May I suggest trying set eqnull off I think it will do what you want but am not quite sure. David Fitts State of Maine Risk Management Division 287-3352 1-800-525-1252
________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Thursday, June 23, 2011 5:05 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Issue with IFNULL and SET ZERO. I have a table with two currency columns LOWPRICE and REGULARPRICE. REGULARPRICE always has a value in it. LOWPRICE sometimes has a value in it. I need to derive a third value (preferably in a computed column) according the following rule: If LOWPRICE is NULL, use REGULARRICE. IF LOWPRICE is NOT NULL, use the lower of LOWPRICE and REGULARPRICE, even if LOWPRICE is $0.00. I can do this with the expression (IFNULL(LOWPRICE, REGULARPRICE, LMIN(LOWPRICE, REGULARPRICE)). That works fine -- but only if SET ZERO OFF is applied. If SET ZERO ON is applied, R:Base loses the ability to distinguish between the values $0.00 and NULL in LOWPRICE. I'm looking for a trick that will be able to distinguish NULL and $0.00 with ZERO set ON. Anyone have any ideas? -- Larry
