Dennis, If you check the divisor first, in your select statement then you don't need to create any view and it will be efficient too regardless how R:base is processing Select Statement. As explained below:
Let Y is dividend and X is divisor. Select Y,X,(ifeq(X,0,0,(Y/X))) from example Here we explicitly put the division result to ZERO, as it should be. Regards Rehan Wyne MIT On Thursday, September 11, 2014 10:31 PM, Stephen Markson <[email protected]> wrote: I think it could process first Step 1: next row Step 2: grab what you need from the row Step 3: check the WHERE Step 4: If WHERE satisfied – display values – else – step 1. Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From:[email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: September-11-14 13:18 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Divide by Zero It certainly should not be processing before the where. There has to be something else going on But, doing the view thing may help isolate the actual problem. From:[email protected] [mailto:[email protected]] On Behalf Of Stephen Markson Sent: Thursday, September 11, 2014 12:09 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Divide by Zero Karen: TableName.ColumnName is fine. Dennis: It appears R:Base is attempting the division before processing the WHERE. Perhaps creating a view leaving out the division but keeping “and activity.standard<>0”, and then selecting the division from the view? Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From:[email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: September-11-14 12:47 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Divide by Zero I've never used table names as part of a correlated update. Maybe it doesn't like it? Try this and see if it works. Also, it doesn't seem like "standard" column is in the t3t table since you don't show it in the output, so I'd try leaving out the "t2" in the divisions): Select t1.*, t2.activity, t2.unitmeas, t2.standard, (tothrs/t2.standard) AS standardunits, (units/(tothrs/t2.standard)*100) AS expectedunits FROM t3t t1, activity t2 WHERE t1.activity = t2.activity and t2.standard <> 0 Karen -----Original Message----- From: Hodges, Dennis <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thu, Sep 11, 2014 11:18 am Subject: [RBASE-L] - Divide by Zero I’m getting a divide by zero from the following select statement. The values for tothrs and activity.standard are all positive real numbers, no zeros or nulls. Does anyone have a clue as to why I’m getting this error. Somehow tothrs/activity.standard is evaluating to 0 as shown below. Select t3t.*,activity.activity,activity.unitmeas,activity.standard,(tothrs/activity.standard) AS standardunits,(units/(tothrs/activity.standard)*100) AS expectedunits FROM t3t, activity WHERE t3t.activity EQ activity.activity and activity.standard <> 0 Tothrs Units Activity UnitMEas Standard StandardUnits 1.5 1.5 033 Manh 1 0 2 2 032 Manh 1 0 6.5 6.5 941 Manh 1 0 4 4 520 Unit 0.83 0 6 11 520 Unit 0.83 0 10 20 520 Unit 0.83 0 3 3 028 Manh 1 0 5 130 526 LnFt 0.25 0 10 14 520 Unit 0.83 0 Dennis Hodges Maintenance Management Manager FDOT Office of Maintenance MS #52 PH: (850) 410-5635 FAX: (850) 410-5511 [email protected] As far as we know, our computer has never had an undetected error. Please note: e-mail may be subject to public disclosure.
