Numeric Comparison

[TimStone]

I am working with a Replace command ( data object ).

Using MsgGet( ) I enter a number ( ie. 4051.25 ). Because it is a string in the GET field ( which must accomodate any type of data type ), I convert it to a Numeric using VAL( ). The problem is that it adds many zeros using VAL ( Harbour ). When I go to seek the value in the database ( 4051.21 ), it can't match because the two numbers are not the same.

I want to limit the number of decimals to 2 for the purpose of matching. I tried using SET DECIMAL TO 2 at the beginning of the function, and then resetting it back to no filter, but that doesn't work out. Also, sometimes the number could need 4 decimal places, or 6.

Any suggestions on how to get the input in the MsgGet( ) to match the contents of the database field for comparison ?

[Antonio Linares]

SET DECIMALS TO 6

[TimStone]

Here is my problem. I have a modified version of Five DBU ( set for just DBF files ) that works well except for the Replace capability.

I build a dialog for creating the replace command using:

Code: Select all | Expand

METHOD ReplaceData(  ) CLASS MLSEditor

 LOCAL aOpsType := { "=", "<", "<=",  "<>", ">=", ">" }
 LOCAL cOldValue := SPACE(50 ), cNewValue := SPACE(50)
 LOCAL uOldValue := SPACE(10), uNewValue := SPACE(10)
 LOCAL cSearchField := SPACE(10), cReplaceField := SPACE(10), cOpsType := "="
 LOCAL lAllRecords := .f., lDoProcess := .f.
 LOCAL nReplaceField := 1 // , aType, aNames, nFlds := 1
 LOCAL cOldType, nField2, cNewType, oRRec, oCbx1, oCbx2, oCbx3, oEdit2, oEdit1
 MEMVAR oBrush, oMFont


  // Create the DIALOG
    DEFINE DIALOG oRRec RESOURCE "FEREPLACE" BRUSH oBrush TRANSPARENT TITLE "Record Data Replacement Selection" FONT oMFont

  // Do we replace all records
    REDEFINE CHECKBOX lAllRecords  ID 601 OF oRRec MESSAGE "Check if you want the value changed in all records"

  // Select the field
  REDEFINE COMBOBOX oCbx1 VAR cSearchField ITEMS ::aNames ID 602 OF oRRec ;
        STYLE CBS_DROPDOWN  MESSAGE "Select the field for selecting records"

  // Next select the operator
  REDEFINE COMBOBOX oCbx2 VAR cOpsType ITEMS aOpsType ID 603 OF oRRec ;
        STYLE CBS_DROPDOWN  MESSAGE "Select the comparison operator"

  // Then get the old value
    REDEFINE GET oEdit1 VAR cOldValue  ID 604 OF oRRec MESSAGE "Enter the current value to match"

  // The replace field
  REDEFINE COMBOBOX oCbx3 VAR cReplaceField ITEMS ::aNames ID 605 OF oRRec ;
        STYLE CBS_DROPDOWN  MESSAGE "Select the field to update"

  // And the new value
  REDEFINE GET oEdit2 VAR cNewValue ID 606 OF oRRec MESSAGE "Enter the new field value"

    REDEFINE BTNBMP RESOURCE "HROK" PROMPT "Process Replace" ID 610 of oRRec NOBORDER TRANSPARENT ;
             ACTION ( lDoProcess := .t., oRRec:end() )

    REDEFINE BTNBMP RESOURCE "HREXIT" PROMPT "Exit Replace" ID 611 of oRRec NOBORDER TRANSPARENT ;
             ACTION  oRRec:end()

    ACTIVATE DIALOG oRRec ON INIT ( oRRec:center(wndmain()) )


    IF lDoProcess
    
      // We want the field number of the replacement field
      cReplaceField := TRIM( cReplaceField)
    nReplaceField := Ascan( ::aNames, cReplaceField )
  // We want the type of data of the selected field
    cNewType := ::aTypes[ nReplaceField ]
    
  // Make sure the new value matches the type of the field and is defined as vNewValue
     IF cNewType = "C"
       uNewValue := cNewValue
     ELSEIF cNewType = "N"
       uNewValue :=  VAL( cNewValue )
     ELSEIF cNewType = "L"
       uNewValue := IIF( cNewValue = "T", .t., .f. )
     ELSEIF cNewType = "D"
       uNewValue := CTOD( cNewValue )
     ELSE
       uNewValue := cNewValue
     ENDIF
     
 
     IF ! lAllRecords
            // vOldValue must be of the right type to do the search
            cSearchField := TRIM( cSearchField )
            nField2 := Ascan( ::aNames, cSearchField )
            // We want the type of data of the selected field
            cOldType := ::aTypes[ nField2 ]
            // Be sure the data type is converted to the field type
            // Make sure the new value matches the type of the field and is defined as vNewValue
            IF cOldType = "C"
              uOldValue := cOldValue
            ELSEIF cOldType = "N"
              uOldValue := VAL( cOldValue )
            ELSEIF cOldType = "L"
              uOldValue := IIF( cOldValue = "T", .t., .f. )
            ELSEIF cOldType = "D"
              uOldValue := CTOD( cOldValue )
            ELSE
              uOldValue := cOldValue
            ENDIF
     ENDIF

      // If desired, execute the replace
      IF MsgNoYes( "Do you wish to do this replacement ?" )
        
        ::oDbf:ReplaceFor( nReplaceField, uNewValue, cSearchField, uOldValue, cOpsType, lAllRecords )
        MsgInfo( "Replace complete" )
        
      ENDIF

      ::oDbf:gotop( )
      
  ENDIF // Do process

 RETURN NIL


 

Toward the bottom you will see ::oDbfr:ReplaceFor( ) as a called method. Here is it's code:

Code: Select all | Expand

/*
Purpose: Do a REPLACE FOR for certain records or all records.

nReplaceField - Number of field that you want to replace with uNewValue
uNewValue     - Value to replace with
cSearchField  - Name of search field. Could be the same as nReplaceField or not
nOldValue     - Value to look for to trigger replacement operation
cOpsType      - Mathmatical value to use ("=", "<", "<=", etc.)
lAllRecords   - Set to .T. to replace all records. Only need nReplaceField and uNewValue when using lAllRecords = .T.
                I.E. You can't specify a condition.

*/

Method ReplaceFor( nReplaceField, uNewValue, cSearchField, uOldValue, cOpsType, lAllRecords ) CLASS TData

   Local bFor, cOldType, lSuccess:=.F., nOldRecNo
   
   Default lAllRecords:=.F.
   
   cSearchField:= upper(cSearchField)
   
   nOldRecNo:= ::recno()
  ::GoTop( )
  ::Lock( )
    
    IF lAllRecords
    
        (::cAlias)->(dbeval( { || FieldPut( nReplaceField, uNewValue ) },,,,, .T. ) )
    
    else
    
        cOldType:=valtype( uOldValue)

        do case
      case ( cOldType = "C" .or. cOldType = "M" ) // Character or Memo type
            bFor := compile( "_Field->" + upper(cSearchField) + cOpsType + '"' + TRIM(uOldValue) + '"' )
            case cOldType = "D"                         // Date type
            bFor := compile( "DTOS(_Field->" + cSearchField +") " + cOpsType + '"' + DTOS(uOldValue)+'"' )
      case cOldType = "L"     // Logical
                bFor:= compile( "_Field->"+ upper(cSearchField) + cOpsType + cValToChar(uOldValue ) )


       case cOldType = "N"  // Numeric
                bFor:= compile( "_Field->"+ upper(cSearchField) + cOpsType + cValToChar(uOldValue ) )


       otherwise
               bFor := ""
                 MsgAlert( cOldType + " Value Not Supported" )
        endcase
        
       (::cAlias)->(dbeval( { || FieldPut( nReplaceField, uNewValue ) }, bFor,,,, .T. ) ) 
      lSuccess:= .T.
    ENDIF
    ::Unlock( )
  ::goto(nOldRecNo)
  
Return lSuccess

//-----------------------------------------------------------------------------//

STATIC FUNCTION Compile( cExp1 )
  // MsgInfo( cExp1, "Inside Compile() Function" ) // For debugging only
Return &( "{||" + cExp1 + "}" )
//-----------------------------------------------------------------------------//

 

This works fine for everything except numerics. When the field in the database is numeric, then I do not get a match, and thus no replacement. FOR ALL OTHER DATA TYPES this process works fine.

I did not write the original function ( an extension of tDatabase ). I need to find a way to be able to do a replace for the numeric fields. I've spent a long time trying different things. Every other option generates an error. This does not generate the error but it does not change the value in the field as desired.

Your observations would be appreciated.

Tim

[nageswaragunupudi]

Code: Select all | Expand

    ELSEIF cNewType = "N"
       uNewValue :=  VAL( cNewValue )
 

AS

Code: Select all | Expand

    ELSEIF cNewType = "N"
       uNewValue :=  ROUND( VAL( cNewValue ), nDec )
 

OR
In this statement

Code: Select all | Expand

case cOldType = "N"  // Numeric
                bFor:= compile( "_Field->"+ upper(cSearchField) + cOpsType + cValToChar(uOldValue ) )
 

Instead of cValToChar,you can use

Code: Select all | Expand

Str( nOldValue, nFieldLen, nFieldDec )

[TimStone]

First nDec is undefined, but even if I give it a value ( ie. 2 ), it still does not do the replace.

I think the issue is with the second method in my post which converts the number to a string, and I don't see how that can get a match with a numeric field.

All other data types work fine, but not the numeric replace. I have looked at other options ( like what is used in the newer versions of fivedbu.prg in samples ) but this layout works nicely and my clients are used to it. Also, Fivedbu.prg is not using database objects.

[nageswaragunupudi]

Actually, the number of trailing zeros after decimal place should not and does not make any difference at all. SET DECIMALS TO has not effect at all.

Here is a sample for testing.
customer.dbf has two records with Salary = 16300.00
Both for conditions viz.

Code: Select all | Expand

"SALARY = 16300"

and

Code: Select all | Expand

"SALARY = 16300.000000"

work EXACTLY the same way and identify the same two records.

This is the test program:

Code: Select all | Expand

function TestFor()

   local cFor, aList, cVal

   USE CUSTOMER NEW VIA "DBFCDX"

   cVal  := "  16300        "
   aList := {}
   cFor  := "SALARY = " + ALLTRIM( cVal )
   ? cFor   // --> "SALARY = 16300"

   DBEVAL( { || AAdd( aList, { FIELD->ID,FIELD->SALARY } ) }, ;
            &( "{||" + cFor + "}" ) )

   ? aList  // --> "{{257,16300.00},{289,16300.00}}"


   cVal  := " 16300.000000  "
   aList := {}
   cFor  := "SALARY = " + ALLTRIM( cVal )
   ? cFor // --> "SALARY = 16300.000000"

   DBEVAL( { || AAdd( aList, { FIELD->ID,FIELD->SALARY } ) }, ;
            &( "{||" + cFor + "}" ) )

   ? aList  // --> "{{257,16300.00},{289,16300.00}}"

   CLOSE DATA

return nil

So your problem is not due to any extra zeros.

[TimStone]

My last comment focused on a different issue / question:

The code is:

Code: Select all | Expand

				bFor:= compile( "_Field->"+ upper(cSearchField) + cOpsType + cValToChar(uOldValue ) )
/code]

bFor becomes a code block.  
The field I am searching is CLSBAL
The type is Numeric
However uOldValue is translated to a character.
Using dbEval(), will it compare a character value to a numeric field and get a match ?  

The code in my original entry works fine except for the Numeric values.  You will see it in the 2nd code segment in that original post, which is the ReplaceFor( ) method / function.