Multiplying in FoxPro

Orongo123 · #1 (**permalink**) 11-28-12, 11:04

Hi

I am new to FoxPro and have no previous experience in database coding. I find it very struggling

Right now I want to take two columns with data from the same file. Multiply these without overwriting anything and save it. The column names are Sum_psigbf and Sum_psigb3. PLEASE HELP, I have been trying to do this for more than an hour and are no where closer to a solution

Code:

SET SAFETY OFF
CLOSE DATABASES
DELETE FILES *.tmp RECYCLE
SET DEFAULT  TO "C:\_moses\Aegon_3097558\MoSes cashflows"
 
USE cf_case ALIAS cf
SELECT      Sum_psigbf as psigbf; 
            Sum_psigb3 as psigb3
FROM cf;
INTO TABLE psigbf.tmp
 
select psigbf, psigb3, (psigbf*psigb3) as AM from table psigbf.tmp
COPY TO AM.dbf

jrbbldr · #2 (**permalink**) 11-28-12, 22:58

"I want to take two columns with data from the same file. Multiply these without overwriting anything and save it. The column names are Sum_psigbf and Sum_psigb3."

Its not hard, but first I need to know where you want the resultant value to go (where to "save it").
Do you want that value put into a Memory variable for subsequent use in an application?
Or do you want that resultant value put into some other field in the table?
Or what?

Regardless, since it looks as though you might want the resultant value in a SQL Query result table, just use very standard SQL Query logic - nothing that is specific to Foxpro.

Maybe something like the following....

Code:

SET SAFETY OFF
CLOSE DATABASES
DELETE FILES *.tmp RECYCLE
SET DEFAULT  TO "C:\_moses\Aegon_3097558\MoSes cashflows"

* --- open desired data table ---
USE cf_case ALIAS cf

* --- Run the query on the table ---
*   Acquire Field1  (assumption is that this is a Numeric or Integer field)
*   Acquire Field2  (assumption is that this is a Numeric or Integer field)
*   Acquire Mult result of Field1 * Field2
* ---------------------------------

SELECT  Sum_psigbf as psigbf,; 
            Sum_psigb3 as psigb3,;
           (Sum_psigbf * Sum_psigb3) as AM;
     FROM cf;
     INTO TABLE psigbf.tmp
* -^- Maybe you might want some WHERE selection criteria in the above SQL Query command (or not) -^-
* --- If not you will get ALL records, not just desired one(s) ---
 
* --- Now select the resultant table (you could have used a memory Cursor instead) ---
SELECT psigbf

* --- Copy it to where you want it ---
COPY TO AM.dbf
* --- Close the resultant table ---
USE

Hopefully that will get you onto the right track.

Alternatively you could have put the result directly into your COPY table

Code:

SELECT  Sum_psigbf as psigbf,; 
            Sum_psigb3 as psigb3,;
           (Sum_psigbf * Sum_psigb3) as AM;
     FROM cf;
     INTO TABLE AM

I might also recommend that you spend some time going over the free on-line FP/VFP tutorials at: Free Visual FoxPro Videos
It is targeted for Visual Foxpro (hopefully that's what you are using), but since much of the code is backwards compatible, the ideas will likely apply to whatever you are using.

Good Luck

Orongo123 · #3 (**permalink**) 11-30-12, 03:45

Thank you! It works just great. This language makes Greek look easy for me. I am doing similar thing from another table and get the error "Command is missing required clause". This for me is not logical, the code is a pure copy paste with changes in the file names. Am I missing something??

Code:

SET SAFETY OFF
CLOSE DATABASES
DELETE FILES *.tmp RECYCLE
SET DEFAULT  TO "C:\"

use cf_case alias cf
SELECT  Sum_psigbf as psigbf,; 
        Sum_psigb3 as psigb3,;
        (Sum_psigbf * Sum_psigb3) as AM;
	FROM cf;
    INTO TABLE psigbf.tmp
select psigbf
COPY TO AM.dbf

use bs_case alias bs
SELECT resovkvot as resovkvot ,;         *Here the error appears
	FROM bs;
    INTO TABLE resovkvot.tmp
select resovkvot
COPY TO H.dbf

CLOSE DATABASES

jrbbldr · #4 (**permalink**) 11-30-12, 09:29

The only difference in FP/VFP SQL Query syntax and 'standard' SQL Query syntax is the semi-colon at the end of the line when a command is continued onto another typed line.

You might notice in the first query that the SQL Query line immediately preceeding the FROM line is terminated with only a semi-colon - NOT a comma + semi-colon

As in 'standard' SQL Query syntax the comma is only used to separate query results FIELDS and does not follow the Last field

And, in the case of FP/VFP, the semi-colon is added at the line end to indicate a line continuation.

So where your new code went wrong was in terminating that line immediately preceeding the FROM line (the Last query results field) with a comma + semi-colon, instead of just the semi-colon.

So your problems here have had much less to do with the Foxpro/Visual Foxpro language than they did with your needing to use 'standard' SQL Query syntax.
If you had followed the rules of 'standard' SQL Query syntax you would have had 99% of this correct.

Code:

SELECT resovkvot as resovkvot;
	FROM bs;
    INTO TABLE resovkvot.tmp

Notice the lack of a comma following the Last query results Field which immediately preceeds the FROM line - as in 'standard' SQL Query syntax.

Good Luck

jrbbldr · #5 (**permalink**) 11-30-12, 09:46

A unique Feature of Foxpro/Visual Foxpro is the ability to work DIRECTLY on the data table records and not have to rely on query results record sets - although working with those works perfectly fine too and is sometimes the right way to go.

For your 2nd query you, since you have no selection criteria in the SQL Query (no WHERE clause and/or parameters), you could have copied the chosen field(s) DIRECTLY to the recipient table.

Code:

use bs_case alias bs
select bs
COPY FIELD resovkvot TO H.dbf
USE

Or you could have used a selection criteria in the COPY line by adding a FOR expression...

Code:

use bs_case alias bs
select bs
COPY FIELD resovkvot TO H.dbf  FOR ThisField = 10
USE

Good Luck

kitaman · #6 (**permalink**) 11-30-12, 10:34

Do they no longer teach that foxpro is a procedural language as well.

Code:

set safety off                                         
set excl on                                            
select b                                               
use am                                                 
* assumes AM.dbf already exists                        
zap                                                    
select a
use cf_case        
do while .not. eof()                                   
        select b                                       
        append blank                                   
        replace psigbf with a->sum_psigbf              
        replace psigb3 with a->sum_psigb3              
        replace psitot with a->sum_psigbf * a->sum_psigb3 
        select a                                       
        skip 1                                         
enddo

jrbbldr · #7 (**permalink**) 11-30-12, 10:55

kitaman - one thing about your code sample introduces more confusion factors

Your use of workspace 'a' and 'b' references instead of using the ALIAS() name can confuse new FP/VFP users to wondering where/how a table with an alias of 'am' suddenly became 'a' or 'b'.

And it causes the new FP/VFP users to have to keep track of what table is in which workgroup - what's in 'a' and what's in 'b'

Better to have shown your code as:

Code:

set safety off                                         
set excl on                                            
USE am IN 0  && USE's table 'am' in the next available workspace - defaults to alias() 'am'
* --- assumes AM.dbf already exists  ---                      
zap                                                    
USE cf_case  IN 0  ALIAS cf && USE's table 'cf_case' in the next available workspace - specifies different alias() 'cf'
* --- then do whatever to the table values ---

And indeed FP/VFP can certainly use procedural code, in addition to OOP, it isn't taught much at all since Micro$oft quit marketing it and stopped new development on it.

The best place a new user can get assistance in their learning process is through the on-line videos I already references and/or through forums like this or, even better, Foxpro/Visual Foxpro specific forums elsewhere like TT or EE

Orongo123 - Good Luck

kitaman · #8 (**permalink**) 11-30-12, 12:42

Just shows that you can learn something new every day.

Orongo123 · #9 (**permalink**) 12-04-12, 04:16

Thanks yall. It works perfectly

jrbbldr · #10 (**permalink**) 12-04-12, 11:43

I'm glad we could help.

The question is...
* Did you merely just USE the code samples we gave you?
* Or did you LEARN from the examples we gave so that you will be able to recognize issues like these (primarily these were NOT Foxpro/Visual Foxpro issues) next time and resolve them yourself?

Hopefully the later.

Good Luck,
JRB-Bldr

Internet Services

Web Development

Digital Marketing

Consumer Tech