Subject Re: Calculated field and Query
From Mervyn Bick <invalid@invalid.invald>
Date Sun, 9 Dec 2018 17:36:30 +0200
Newsgroups dbase.getting-started
Attachment(s) sql_calc.wfm

On 2018-12-09 4:55 PM, Dirk wrote:
> Hello goodday
 >
> i am trying to put data from a query to an excel file
 > i was struggling with select * from kilom with this query i couldn't
> bring on the order i would in excel list
 >
> after two coffees i got it to replace the query with the fieldnames
> instead of wildcart *  put in order to get in a way i like to have in
> excel list but
 > i liketo put a couple of calculated fields in the query, but query
> doesn't accept it ( not in the *.dbf file)
 >
> but how to get this calc fields value in the excel file
 >
> thanks for some advice
 >
> Dirk
It depends on what calculations you want to make but as long as you
don't need anything fancy like trig functions you should be OK.

The attached example shows the name of an existing field changed from e
to Fahrenheidt for the rowset together with 3 calculated fields.

Mervyn.




if file('calc_values.dbf')
drop table calc_values
endif

if not file('calc_values.dbf')
   create table calc_values  (id autoinc,a numeric(10,2),b numeric(10,2),;
     c numeric(10,2),d numeric(10,2),e numeric(10,2))

   insert into calc_values  (a,b,c,d,e) values (4.00,5.00,8.00,12.00,76)
   insert into calc_values  (a,b,c,d,e) values (5.00,9.00,45.00,100.00,50)
endif

** END HEADER -- do not remove this line
//
// Generated on 2018-12-09
//
parameter bModal
local f
f = new sql_calcForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class sql_calcForm of FORM
   with (this)
      height = 16.0
      left = 21.4286
      top = 1.7273
      width = 190.2857
      text = ""
   endwith

   this.CALC_VALUES1 = new QUERY(this)
   with (this.CALC_VALUES1)
      left = 4.0
      top = 1.0
      width = 10.0
      height = 1.0
      sql = 'select a,b,c,d,e as Fahrenheit,5*(e-32)/9 as Centigrade_calc,'
      sql += 'b-a as f_calc,(d-c)/d*100 as percent_c_of_d_calc from "calc_values.DBF"'
      active = true
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.calc_values1.rowset
      height = 4.0
      left = 4.0
      top = 4.5
      width = 179.0
   endwith

   this.rowset = this.calc_values1.rowset

endclass