Thanks Jim: Looks like 'null management' should be a coded global strategy - the 'long-term' solution implied by Larry.
Bruce > -------- Original Message -------- > Subject: [RBASE-L] - Re: Fail: Computed Column Shows all Nulls (-0-) > From: James Bentley <[email protected]> > Date: Mon, February 13, 2012 11:21 am > To: [email protected] (RBASE-L Mailing List) > > > I do not like to futz with system settings "SET ZERO ON" etc as they may have > unintended consequences in other parts of the system. > Instead try the following as your computed expression; > "((IFNULL(COL1,0,COL1)) + (IFNULL(COL2,0,COL2)) + (IFNULL(COL3,0,COL3)) + > (IFNULL(COL4,0,COL4)) + (IFNULL(COL5,0,COL5)) + (IFNULL(COL6,0,COL6)))". > This expression returns a ZERO for any NULL columns or the actual col value > and shod always return a value of the col sums instead of null. > > > Jim Bentley > American Celiac Society > [email protected] > tel: 1-504-737-3293 > > > >________________________________ > > From: Lawrence Lustig <[email protected]> > >To: RBASE-L Mailing List <[email protected]> > >Sent: Monday, February 13, 2012 12:25 PM > >Subject: [RBASE-L] - Re: Fail: Computed Column Shows all Nulls (-0-) > > > > > >You will need to make sure you have > > > > > >SET ZERO ON > > > > > >in effect whenever you update this table. At that time the calculated value > >will be recalculated and, with that setting in effect, the NULLs will count > >as 0 (right now you're seeing the effect of NULL causing the calculation to > >produce an undetermined results). > > > > > >To fix the problem in the short term: > > > > > >SET ZERO ON > >UPDATE YourTable SET SomeColumn = SomeColumn > >SET ZERO OFF > > > > > >-- > >Larry > > > > > > > >________________________________ > > From: Bruce Chitiea <[email protected]> > >To: RBASE-L Mailing List <[email protected]> > >Sent: Monday, February 13, 2012 1:19 PM > >Subject: [RBASE-L] - Fail: Computed Column Shows all Nulls (-0-) > > > >All: TIA > > > >RBG91_64.EXE SU > >Build: 9.1.5.20103 > >Windows 7 Ultimate > > > >Problem: > >The null (-0-) value appears in a computed column across ALL rows in a > >browsed table, where the computed column has been added after the table > >is populated with data. > > > >Creating the computed column: > >1. Create/insert a computed column in an existing data-populated table. > >2. Insert the computed column below all columns used in the calculation. > >3. All columns share the data type CURRENCY. > >4. Simple expression: "(col1 + col2 + col3 + col4 + col5 + col6)". > >5. There is at least one null (-0-) value in EACH row within the column > >set [col1 ... col6]. > >6. There is no row with ALL null values in column set [col1 ... col6]. > > > >Thoughts? > > > >Bruce Chitiea > >SafeSectors, Inc. > >909.238.9012 cell > > > > > > > > > > > > > > > > > > > > > >
