Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2012
    Posts
    24

    Multiplying in FoxPro

    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

  2. #2
    Join Date
    Nov 2002
    Posts
    108
    "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

  3. #3
    Join Date
    Nov 2012
    Posts
    24
    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

  4. #4
    Join Date
    Nov 2002
    Posts
    108
    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

  5. #5
    Join Date
    Nov 2002
    Posts
    108
    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

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,001
    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

  7. #7
    Join Date
    Nov 2002
    Posts
    108
    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

  8. #8
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,001
    Just shows that you can learn something new every day.

  9. #9
    Join Date
    Nov 2012
    Posts
    24
    Thanks yall. It works perfectly

  10. #10
    Join Date
    Nov 2002
    Posts
    108
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •