ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to compare these two fields (character and decimal)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to compare these two fields (character and decimal)

    Exec Sql
    Select fld1 Into :WFLD1 FROM ECH
    Where FLD1 = CHAR(:fld2)

    FLD 1 23 OPEN DATA TYPE WHILE FLD 2 8P. How to solve this issue that when fld 1 have a value of 0005764 it will be able to determine that 5764 in FLD2 is the same.

  • #2
    You could trim Leading zeros from FLD1 :

    Exec Sql
    Select fld1 Into :WFLD1 FROM ECH
    Where strip(FLD1 , LEADING , '0') = CHAR(:fld2) ;

    Or convert FLD1 to a decimal number :

    Exec Sql
    Select fld1 Into :WFLD1 FROM ECH
    Where FLD1 <> ' ' and translate(FLD1 , ' ' , '0123456789') = ' '
    and decimal(FLD1 , 8 , 0) = :FLD2 ;

    Ringer

    Comment


    • #3
      Assumed the value in fld1 is 8 digits (always with leading zeros and without decimal positions) and fld2 is defined as DEC(8, 0)), just modify your Query as follows:
      PHP Code:
      Exec SQL Select fld1 Into :WFLD1
                   FROM ECH
                   Where FLD1 
      DIGITS(:fld2
      Birgitta

      Comment


      • #4
        The latter is valid as long as one can assume that all of the leading positions are 0 and length of the field is the same.

        Code:
        Monitor;                      
          If %Dec(Wfld1:9:0) = Nfld1;   
            DSPLY 'They are equal';     
          Else;                         
            DSPLY 'They are not equal'; 
          Endif;                        
        On-Error;                     
          DSPLY 'Are not equal';      
        EndMon;

        Comment

        Working...
        X