(IFNULL(LOWPRICE, REGULARPRICE, LMIN(IFNULL,LOWPRICE,0,LOWPRICE), REGULARPRICE))
Emmitt Dove Converting Systems Architect Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Thursday, June 23, 2011 17:05 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
