Subject Re: dataq modules - calculated fields From Gaetano De Luisi <gaetanodd@hotmail.com> Date Wed, 08 Jul 2020 22:04:37 -0400 Newsgroups dbase.getting-started Thank you Mervyn, those details help re-anchor the concepts as I am diving back into a bit of coding! Mervyn Bick Wrote: > On 2020-07-08 14:44, Gaetano De Luisi wrote: > > > > Hi Akshat, > > > > I have playing around with various syntaxes and it seems that once you select *, you can't add anything to the selection. There aren't many fields so I might still go with this option and state all fields individually + their calculated version. Thanks for your help! > > > > See below. > > It is good practice with SQL servers, especially with very big tables, > to only fetch the minimum of data.  If you don't need all the fields > don't include them.  It's a good habit to adopt when using .dbf files. :-) > > > PS, select energydata.* or energydata.dbf.* from "energydata.dbf" does not work for me, neither at command line level, nor in the designer. > > > > The following command produces an "invalid field name error": > > select energydata.energyProducedWh from "energydata.dbf" > > > But this one works, I need to omit the file extension in the file name: > > select energydata.*,energyProducedWh*12 as power from "energydata" > > > > My apologies for the incorrect example I gave you yesterday.  If you use > the * wildcard and then want to include calculated fields (or a second > copy of a field with a different name) you need to qualify the wildcard > with the tablename or a table correlation name. > > sql = 'select e.*,12*energyProducedWh as hourlyWhEquivalent from > "energydata.dbf" e' > > sql = 'select energydata.*,12*energyProducedWh as hourlyWhEquivalent > from energydata' > > sql = 'select e.*,12*energyProducedWh as hourlyWhEquivalent from >   energydata e' > > The following won't work as energydata.dbf is a filename not a > tablename.  If you use a correlation name with a filename as in the top > example dBASE assigns the table name to the correlation name. > > sql = 'select energy.*,12*energyProducedWh as hourlyWhEquivalent from > "energydata.dbf"' > > > The double quotes around energydata.dbf are only needed if there is a > space in the filename.  dBASE doesn't allow spaces in filenames but > Paradox does so the designers stream out the double quotes "just in > case".  They can be omitted without a problem if you are using dBASE > files.  You can use either a filename (energydata.dbf) or a tablename > (energydata) in you select statement. > > > Mervyn. > > > >