How to substring and cast in select where statement on iSeries with RPG free

65 pts.
Tags:
iSeries
RPG
SQL
Hi. I'm relatively new to free form RPG and I rewrote a program to be RPG free and SQL. I'm having problems with my where clause. I'm trying to substring the second byte on a 2 digit packed field. I've tried to use the CAST statement to convert the packed field to CHAR and then do the SUBSTRING on it. When it runs in the program I get sqlstt = 22023 and sqlcode = -802. When I try to run the sql statement separately interactively, I get "Selection error involving field *N. " and in the joblog I get "Select or omit error on field Cast(SFAM_1.SPRPID AS Char(1) CCSID 37) member SFAMLNR." It appears to be a problem with my CAST statement, but I can't figure out what is wrong. Any help would be greatly appreciated. Thanks!!


Software/Hardware used:
iSeries AS/400
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 12  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • raynesnf

    Here is my full SQL statement:

    Select SFBIS1, SFBIS2, SLAMT, SFCINC, SPRICT, COD2,  SFIRST
    From SFAMLNR  Where SFBIS2 = '942' AND PURLCD = 'P' AND CAST
    (DIGITS(SPRYR)||DIGITS(SPRMON) AS DECIMAL(6,0)) BETWEEN 201601
     AND 201604 AND SINSCO <> 0015 AND SINSCO <> 0016  AND
    SUBSTRING(CAST(SPRPID AS CHAR(1)),2,1) = '1' AND SOO = 'PA'   

    65 pointsBadges:
    report
  • CharlieBrowne
    Would you please give us the field definitions.
    62,385 pointsBadges:
    report
  • raynesnf
    SFBIS1  3,0 packed
    SFBIS2  3    char
    SLAMT  8,0 packed
    SFCINC 6,0 packed
    SPRICT  6,0 packed
    COD2     1   char
    65 pointsBadges:
    report
  • raynesnf
    PURLCD   1    char
    SPRYR      4,0 packed
    SPRMON   2,0 packed
    SINSCO     4,0 packed
    SPRPID     2,0  packed
    SOO          2   char
    65 pointsBadges:
    report
  • philpl1jb

    don't think you can concat digits

    (DIGITS(SPRYR)||DIGITS(SPRMON) AS DECIMAL(6,0)) BETWEEN 201601
     AND 201604

    maybe something like this

    SPRYR * 100 + sprmon BETWEEN 201601 AND 201604


    54,090 pointsBadges:
    report
  • raynesnf
    Actually that part of the statement works fine.  The part that I'm getting the error on is:

    SUBSTRING(CAST(SPRPID AS CHAR(1)),2,1) = '1'

    Thanks!
    65 pointsBadges:
    report
  • ToddN2000
    I've used this to convert our item number( a 5 with 2 decimal positions)

    WHERE SUBSTRING(digits(O6ITEM),4,2) = '00'

    Is looking for the high level item with a .00 ending.
    134,635 pointsBadges:
    report
  • raynesnf
    I replaced this part of my where clause:
    SUBSTRING(CAST(SPRPID AS CHAR(1)),2,1) = '1'
    with:
    SUBSTRING(DIGITS(SPRPID,2,1) = '1'
    and now I'm getting this error message:
    "Token = was not valid. Valid tokens: , FROM."

    I get this same msg whether it's running in the program or whether I'm trying to execute the command by itself interactively. I'm working on the iSeries using RPG free.  I don't know it that makes any difference.

    Thanks!
    65 pointsBadges:
    report
  • CharlieBrowne

    Why CAST?

    Select SFBIS1, SFBIS2, SLAMT, SFCINC, SPRICT, COD2,  SFIRST
    From SFAMLNR 

    Where SFBIS2 = '942' AND

    PURLCD = 'P' AND

    SPRYR = 2016 AND

    SPRMON *LE 05 AND      

    SINSCO <> 0015 AND

    SINSCO <> 0016 AND

    SUBSTRING(CAST(SPRPID AS CHAR(1)),2,1) = '1' AND

    SOO = 'PA'   

    62,385 pointsBadges:
    report
  • raynesnf
    I see that I had a syntax error on the statement.  I changed it to:
    SUBSTRING(DIGITS(SPRPID),2,1) = '1'
    and it works great!  Thanks so much for all your help!!
    65 pointsBadges:
    report
  • ToddN2000
    Glad we could help. Working with mismatched data type can be a headache sometimes. Someone out there usually has a solution.
    134,635 pointsBadges:
    report
  • BigKat
    The casting error was PROBABLY that you were casting a 2 digit field into a 1 character field
    9,460 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: