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

    Unanswered: Usinf Iif in FoxPro

    Hi

    I have some conditions that will set the value for a column, for this I am using Iif. It is working good if the conditions is numeric, lets say "row value in column"<100 then return value. But now I want to use the conditions based on another column. So

    Code:
    USE Sourcetmp.tmp
    	ALTER TABLE Sourcetmp.tmp
    	ColA	WITH IIF(year >= 0 AND ColB> 0, ColA, 0)
    	COPY TO results.dbf
    CLOSE TABLES
    Right now ColA only contains zeros even though the conditions is true. I don't see what I have missed... please help.....

  2. #2
    Join Date
    Nov 2002
    Posts
    128
    Provided Answers: 1
    In your code above I don't understand why you are using the command ALTER TABLE.

    If you look into your FP/VFP Help you will find that command is used to change the table STRUCTURE, not its field values.

    You would use the REPLACE command to change the field values of an existing Cursor/Table

    First and foremost, I'd suggest that you spend some time on any of the FP/VFP functions that you are interested in using by looking at the FP/VFP Help on the topic.
    From the FP/VFP Command window type HELP IIF()

    Second, I'd suggest that you spend some time looking at the free on-line video tutorials at: Free Visual FoxPro Videos

    Next, I don't know why you would be using a non-DBF file extension for a data table.
    If you want to designate a data cursor/table as a temporary table use a unique filename, but leave the file extension alone.

    Code:
    * --- 'Open' Sourcetmp.dbf in the next available workspace ---
    USE Sourcetmp IN 0  
    SELECT Sourcetmp
    * --- Alter ALL ColA values ---
    * --- If (Year >= 0 AND ColB > 0) is TRUE leave values ColA alone ---
    * --- If (Year >= 0 AND ColB > 0) is FALSE replace with new value = 0 ---
    *
    REPLACE ALL ColA WITH IIF(Year >= 0 AND ColB > 0, ColA, 0)
    Again, since you are copying away the results anyway, this could have been done with a SQL Query
    Code:
    * --- 'Open' Sourcetmp.dbf in the next available workspace ---
    USE Sourcetmp IN 0  
    
    * --- Define Fully-pathed Destination of Query Results ---
    cResultDBF = "C:\VFP Temp Dir\Results.dbf"
    
    SELECT IIF(Year >= 0 AND ColB > 0, ColA, 0) AS ColA;
       FROM SourceTmp;
       INTO TABLE (cResultDBF)
    
    SELECT SourceTmp
    USE
    Good Luck

Posting Permissions

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