ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

how to use LEN SQL's command in RPGIII or ASSET?

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

  • how to use LEN SQL's command in RPGIII or ASSET?

    Dear Sirs or Madams,
    I am making a program need to use SQL command in RPGIII or ASSET.
    I write:
    C/EXEC SQL
    C+ INSERT INTO ABO025 (BO2502,BO2503,BO2510)
    C+ SELECT ICPROD,ICFAC,ICDRAW FROM CICL89 WHERE
    C+ LEN(CICL89.ICDRAW)=4
    C/END-EXEC

    I compile, it is OK, But when I call program and view report, it has a message report that:LEN IN [B]*LIBL TYPE *N NOT FOUND.
    I need to get the length of ICDRAW field in CICL89 file, but i do not know the command to get length of field in RPGIII or ASSET.
    Pls if anyone know how to get length of field, pls tell me.
    Thanks in advance.
    Best regards.

  • #2
    Re: how to use LEN SQL's command in RPGIII or ASSET?

    Originally posted by bienvu
    Dear Sirs or Madams,
    I am making a program need to use SQL command in RPGIII or ASSET.
    I write:
    C/EXEC SQL
    C+ INSERT INTO ABO025 (BO2502,BO2503,BO2510)
    C+ SELECT ICPROD,ICFAC,ICDRAW FROM CICL89 WHERE
    C+ LEN(CICL89.ICDRAW)=4
    C/END-EXEC

    I compile, it is OK, But when I call program and view report, it has a message report that:LEN IN [B]*LIBL TYPE *N NOT FOUND.
    I need to get the length of ICDRAW field in CICL89 file, but i do not know the command to get length of field in RPGIII or ASSET.
    Pls if anyone know how to get length of field, pls tell me.
    Thanks in advance.
    Best regards.
    This program uses systems API's to return all fields and their length from a usespace.
    Feel free to use it....







    Thanks
    Jamie
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

    Comment


    • #3
      Re: how to use LEN SQL's command in RPGIII or ASSET?

      Instead of using LEN, try LENGTH instead.

      Comment


      • #4
        Re: how to use LEN SQL's command in RPGIII or ASSET?

        Originally posted by pjk
        Instead of using LEN, try LENGTH instead.
        Why would you care about the length of a field....

        Or this saying that no matter the length of field only return data with the
        first 4 positions populated?

        Thanks
        Jamie
        All my answers were extracted from the "Big Dummy's Guide to the As400"
        and I take no responsibility for any of them.

        www.code400.com

        Comment


        • #5
          Re: how to use LEN SQL's command in RPGIII or ASSET?

          Originally posted by jamief
          Or this saying that no matter the length of field only return data with the first 4 positions populated?
          That was my impression. Actually it may need to be like this: LENGTH(RTRIM(CICL89.ICDRAW)=4

          This assumes that the field is alphanumeric, and in a fixed length field. Otherwise, the LENGTH value will always be the max length of the field (without a RTRIM applied to strip off the trailing blanks).

          If it is a numeric field, then it can still be done this way: LENGTH(RTRIM(CHAR(CICL89.ICDRAW))=4

          Comment


          • #6
            Re: how to use LEN SQL's command in RPGIII or ASSET?

            Originally posted by pjk
            That was my impression. Actually it may need to be like this: LENGTH(RTRIM(CICL89.ICDRAW)=4

            This assumes that the field is alphanumeric, and in a fixed length field. Otherwise, the LENGTH value will always be the max length of the field (without a RTRIM applied to strip off the trailing blanks).

            If it is a numeric field, then it can still be done this way: LENGTH(RTRIM(CHAR(CICL89.ICDRAW))=4

            Thanks all,
            It is exactly, we need to use Length command with Trim or Rtrim command to strip off the trailing blanks.
            one more time, thanks again to everyone.
            Have a nice day,
            Best regards.
            Bienvu.

            Comment

            Working...
            X