Substringing long character length field

750 pts.
Tags:
AS 400
After doing a DSPUSRPRF command and putting the data into a file, there is a field called Special Authorities which is a 150 character length field. I need to take this field and break it up to create a separate field for each value that this field has. So if there are entries like, *ALLOBJ  *JOBCTL *SECOFR etc., I would need to create fields for the maximum number of entries for that 150 character length field, something like &spec1, &spec2, &spec3 etc… How would I go about substringing out that long character length field? The reason I need to break up the field is a user has some program that needs to use those value(s) in one of his programs. He needs to be able to access each entry separately and I just need to provide the file for him.


Software/Hardware used:
V7R1M0
1

Answer Wiki

Thanks. We'll let you know when a new response is added.
TRy this, run the command

DSPUSRPRF USRPRF(MYPROF) OUTPUT(*OUTFILE) OUTFILE(MYLIB/PROFILE)


Then try using the Excel addin to extract from the AS/400 and run a native SQL like this

select s.* , 
substring(UPSPAU,1,10) as auth1,
substring(UPSPAU,11,10) as auth2,
substring(UPSPAU,21,10) as auth3,
substring(UPSPAU,31,10) as auth4,
substring(UPSPAU,41,10) as auth5,
substring(UPSPAU,51,10) as auth6,
substring(UPSPAU,61,10) as auth7,
substring(UPSPAU,71,10) as auth8,
substring(UPSPAU,81,10) as auth9,
substring(UPSPAU,91,10) as auth10,
substring(UPSPAU,101,10) as auth11,
substring(UPSPAU,111,10) as auth12,
substring(UPSPAU,121,10) as auth13,
substring(UPSPAU,131,10) as auth14,
substring(UPSPAU,141,10) as auth15
from MYLIB/PROFILE s

Discuss This Question: 20  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.
  • mmanley
    There are a couple of ways to approach this. First, it looks like each of those values takes up a fixed number of characters (10). If that's accurate, you could redefine an array on top of the field with 15 elements and a length of 10 characters. Then just loop through the array, ignoring any blank entries. The other option would be to search the string for non-blank strings and loop through until all you had left was blanks. That takes a bit more logic, so I'd recommend overlaying an array on the field.
    470 pointsBadges:
    report
  • azohawk

    In this case, all of the special authorities are granted 10 characters, so you know that they start at character position xx1.

    A couple of options, you could build a loop that starts at 1 for the substring then adds 10 to each loop for the starting position.

    Another approach is to put the data into an array or data structure.

    Another approach is to search for the next '*' character as the starting postion.

    4,000 pointsBadges:
    report
  • mmanley
    It looks like that field is made up of 10 character values (15 of them). The easiest process would be to overlay a 15 element 10 character array on the field, and then loop through the values.
    470 pointsBadges:
    report
  • ToddN2000
    It can be done a few ways. How are you building the file, SQL , query or via a program? It all comes down to where this file is going to be for the user to access. Let us know and we can provide some more examples.
    127,840 pointsBadges:
    report
  • Rrbond07
    If you can provide some code examples that would be very helpful. Once this file is created, it is going to be transferred from the AS400 to a p.c. file, and then loaded into an Excel spread sheet. I would like to do it with query if possible, but the array option sounds pretty good. Can you provide some code that would accomplish this. Thanks for your help.
    750 pointsBadges:
    report
  • TheRealRaven
    An "array" can't exactly be DCL'd in i 7.1 CL, but you can process a large variable as if it was an array by using a *PTR along with a *BASED variable. In your case, the based variable would be CHAR(10), and you'd step through the list by offsetting 10 bytes at a time.
    33,050 pointsBadges:
    report
  • Rrbond07
    Well, let me be more detailed.

    I am hoping someone can tell me what I am doing wrong with my code.

     

    First I did command DSPUSRPRF for all profiles on the system and created a file called PROFILES with all the information for each profile of which there are 1492 profiles. 

    I then used Query to create another file called PROFILE with only fields UPUPRF, UPTEXT, UPSTAT, UPSUPG and UPPSOD.

    I then copied PROFILE to another file called PROFILE2  as this file will be used for input in my program, and cleared  PROFILE2 since I just wanted the file structure.

    Now, a number of these profiles have several Supplemental Groups added (field UPSUPG) and each one must be listed in the PROFILE2 file as a separate entry  rather than listed across.  Field UPSUPG is a 150 *CHAR field, enough for 15, 10 character supplemental groups to be added to the user profile.

    Now, for at least testing I am only doing the first record and value to see if my INSERT is working properly.  If I put the “IF” statement in my code it doesn’t insert anything into PROFILE2, if I take it out it will insert all the fields except for value &SUP1 which should have the value of the substringed field UPSUPG which should hold positions 1 through 10 of that field, but the value does not get inserted.  I’ve even DFU’d  the file PROFILE with characters of my own, but still it won’t insert a value into the file.

     

    I know I will have to duplicate the code 15 times, but I am only familiar to a point with CL.

     

    What on earth am I doing wrong?

     

    PGM                                                              

     DCLF       FILE(BONDELIB/PROFILE)                   

                                                                     

     DCL        VAR(&SUP1) TYPE(*CHAR) LEN(10)           

    DCL        VAR(&UPSUPG) TYPE(*CHAR) LEN(150)        

                                                                     

                                                                     

    READ:   RCVF RCDFMT(*FILE)                                       

            MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(END))         

                                                                     

                                                                      

            CHGVAR     VAR(%SST(&UPSUPG 1 10)) VALUE(&SUP1)          

                                                                     

       IF COND(&SUP1 *NE ' ') THEN(DO)                               

                                                                      

    RUNSQL SQL('INSERT INTO PROFILE2 (UPUPRF, UPTEXT, +              

            UPSTAT, UPSUPG, UPPSOD) VALUES('''||&UPUPRF||''', +         

            '''||&UPTEXT||''','''||&UPSTAT||''', +                      

            '''||&SUP1||''','''||&UPPSOD||''')') COMMIT(*NC)            

                                                                        

     ENDDO                                                              

      /*          GOTO       CMDLBL(READ)       */                      

                                                                        

     END: ENDPGM                                                        

    750 pointsBadges:
    report
  • GregManzo
    Step one, you have this statement backwards:
    CHGVAR     VAR(%SST(&UPSUPG 1 10)) VALUE(&SUP1)  
    Clearly, what you wanted was:
    CHGVAR     VAR(&SUP1) VALUE(%SST(&UPSUPG 1 10)) 
    2,915 pointsBadges:
    report
  • Rrbond07
    Greg, I made the changes that you stated, and I was able to get the program to work, but to a point.  There are 1462 profiles listed in file PROFILE, but it is only writing so many to file PROFILE2 before I get an SQL error.  I've listed the program I am using.  Later I will have to change it to list the recorded multiple times depending on the number of supplemental groups the profile has so right now I wanted to record at least the first value for each profile.  Like I said I was getting an error on the sql statement.  Can you tell what I am doing incorrectly?

    FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7

            *************** Beginning of data *************************************

     PGM                                                                  


                                                                    
     
    DCLF       FILE(BONDELIB/PROFILE)                                   

                                                                       

     DCL        VAR(&SUP1) TYPE(*CHAR) LEN(10)                           

                                                                      

     READ:   RCVF RCDFMT(*FILE)                                            

     MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(END))              

      CHGVAR     VAR(&SUP1) VALUE(%SST(&UPSUPG 1 10))               

     IF COND(&SUP1 *NE ' ') THEN(DO)                                     

     RUNSQL     SQL('INSERT INTO PROFILE2 (UPUPRF, UPTEXT, +  

     UPSTAT, UPSUPG) VALUES(''' || &UPUPRF || +  

        ''',''' || &UPTEXT || ''',''' || &UPSTAT +  

      || ''',''' || &SUP1 || ''')') COMMIT(*NONE)  

     ENDDO                                                                

     GOTO       CMDLBL(READ)                                                

    END: ENDPGM                                                            

            ****************** End of data ****************************************

    Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:8.0pt; mso-para-margin-left:0in; line-height:107%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

    SEV  DATE      TIME             FROM PGM     LIBRARY     INST     TO PGM     

       To module . . . . . . . . . :   TESTCL                                     

       To procedure  . . . . . . . :   TESTCL                                     

       Statement . . . . . . . . . :   1400                                       

       Message . . . . :     1400 - RUNSQL SQL('INSERT INTO PROFILE2 (UPUPRF, UPTE

         UPSTAT, UPSUPG) VALUES(''PRDINQSXP '',''Satyaranjan Prusty - Prod inq''  

                       '',''*ENABLED  '',''PRDINQ    '')') COMMIT(*NONE)          

     30   04/09/18  11:52:01.564918  QSQPLIST     QSYS        *STMT    QSQRPARS   

       From module . . . . . . . . :   QSQPLIST                                   

       From procedure  . . . . . . :   QSQPLIST                                   

       Statement . . . . . . . . . :   2877                                       

       To module . . . . . . . . . :   QSQRPARS                                   

       To procedure  . . . . . . . :   NOTFOUNP                                    

       Statement . . . . . . . . . :   5946                                       

       Message . . . . :   Token ',' was not valid. Valid tokens: ) ,.            

       Cause . . . . . :   A syntax error was detected at token ','.  Token ',' is

    ....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1..

        not a valid token.  A partial list of valid tokens is ) ,.  This list    

        assumes that the statement is correct up to the token.  The error may be 

        earlier in the statement, but the syntax of the statement appears to be  

        valid up to this point. Recovery  . . . :   Do one or more of the followi

        and try the request again: -- Verify the SQL statement in the area of the

        token ','. Correct the statement.  The error could be a missing comma or 

        quotation mark, it could be a misspelled word, or it could be related to 

        order of clauses. -- If the error token is <END-OF-STATEMENT>, correct th

        SQL statement because it does not end with a valid clause.               

    30   04/09/18  11:52:01.565070  QSQSCHEM     QSYS        1815     TESTCL     

      To module . . . . . . . . . :   TESTCL                                     

      To procedure  . . . . . . . :   TESTCL                                      

      Statement . . . . . . . . . :   1400                                       

      Message . . . . :   RUNSQLSTM command failed.                              

      Cause . . . . . :   Errors were found while processing the source for the  

        RUNSQLSTM command.  The listing print file is *N in *N. Recovery  . . . :

    +....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1..

         Review the messages on the listing.  Either correct the problems or speci

         a higher severity on the ERRLVL parameter. Try the request again. Technic

         description . . . . . . . . :   The RUNSQLSTM command found errors with a

         severity greater than the requested ERRLVL.                              

     99   04/09/18  11:52:01.565345  QCLXERR      QSYS        00DA     QCLXERR    

       Message . . . . :   SQL9010 received by procedure TESTCL. (C D I R)        

       Cause . . . . . :   ILE Control language (CL) procedure TESTCL in module   

         TESTCL in program TESTCL in library BONDELIB detected an error at stateme

         number 0000001400.  Message text for SQL9010 is: RUNSQLSTM command failed

         Use F10 (if available) or the Display Job Log (DSPJOBLOG) command to see 

         messages in the job log for a more complete description of what caused th

         error.  If you still are unable to solve the problem, please contact your

         technical support person. Recovery  . . . :   This inquiry message can be

         avoided by changing the procedure. Monitor for the error (MONMSG command)

         and perform error recovery within the procedure.  To continue, choose a  

         reply value. Possible choices for replying to message . . . . . . . . . .

    +....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1..

         . . . . :   C -- Cancel the CL procedure. D -- Dump the CL procedure     

         variables and cancel the procedure. I -- Ignore the failing command. R --

         Try the failing command again.                                           

          04/09/18  11:52:11.275317  QMHDSMSS     QSYS        0C02     QCLXERR    

       Message . . . . :   C                                                      

                   Job Log                             WEBDEV   04/09/18 11:52:11 

     PRDADMXXX       User  . . . . . . :   PRDADMSXB    Number . . . . . . . . . .

     PRDADMXXX       Library . . . . . :   QGPL                                   

     SEV  DATE      TIME             FROM PGM     LIBRARY     INST     TO PGM     

     30   04/09/18  11:52:11.275487  QLEAWI       QSYS        *STMT    QCMD       

       From module . . . . . . . . :   QLEDEH                                     

       From procedure  . . . . . . :   Q LE leDefaultEh2                          

       Statement . . . . . . . . . :   175                                        

       Message . . . . :   Application error.  SQL9010 unmonitored by TESTCL at   

         statement 0000001400, instruction X'0000'.                               

       Cause . . . . . :   The application ended abnormally because an exception  

    +....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1..

         occurred and was not handled.  The name of the program to which the      

         unhandled exception is sent is TESTCL TESTCL TESTCL. The program was stop

         at the high-level language statement number(s) 0000001400 at the time the

         message was sent.  If more than one statement number is shown, the progra

         is an optimized ILE program.  Optimization does not allow a single statem

         number to be determined.  If *N is shown as a value, it means the real va

         was not available. Recovery  . . . :   See the low level messages previou

         listed to locate the cause of the exception.  Correct any errors, and the

         try the request again.                        

    Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:8.0pt; mso-para-margin-left:0in; line-height:107%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}
    750 pointsBadges:
    report
  • ToddN2000
    The "token" error points to the formatting/syntax having an error. This is usually something to do with a punctuation character. Just a heads up on a problem I have had to deal with is, if your data contains things like a single quote or comma, parenthesis it will cause your SQL command string to fail. Things like a name O'Brien or in a phone#  (215)-345-5463.
    Not sure if the - in 'Satyaranjan Prusty - Prod inq''  may be causing the problem. 
    127,840 pointsBadges:
    report
  • Rrbond07
    You know ToddN200, you may be right. It is hitting the apostrophe in the text field and causing my RUNSQL statement to fail. At least, that is the first problem I see. Why would an apostrophe within a text field effect an SQL statement? Anyway, I will go through the PROFILE file and look at the text descriptions and remove any that I may see. I've never had to substring a long character field before and I don't know how the one person before me did it. I am sure it is something simple, but I am turning it into a project.
    750 pointsBadges:
    report
  • GregManzo
    Because you are building up the SQL command by concatenating values, any embedded punctuation characters can prematurely end your statement. Probably best explained by the XKCD cartoon, google 'Bobby tables' and you'll find it.
    You will need to 'clean up' your text descriptions before building the statement.
    2,915 pointsBadges:
    report
  • ToddN2000
    @gregmanzo: thanks for the tip on the cartoon...I never saw that before...LMAO...Yes thats part of the problem when you are dealing with data access and processing from multiple OS and databases... DB2 has always been a thorn in my side when dealing with SQL.
    Things like "select top 100" vs "fetch first 100 rows only".
    127,840 pointsBadges:
    report
  • ToddN2000
    When I'm passing things that may contain a single quote like a company name and am build the SQL string to load a DB2 file I have to do something like this to keep the single quote. Don't ask me why but the 5 single quotes work.
    Value = dr("SoldToCompany").replace(Chr(39), "' + ''''' + '").ToString.ToUpper.Trim
    127,840 pointsBadges:
    report
  • Rrbond07
    Yup guys! Because of a single quote in the text description it was causing my SQL to fail. There were four records that I DFU'd then ran my program and success. Never would have thought about a single quote causing an issue. Thank you all for all your help.
    750 pointsBadges:
    report
  • ToddN2000
    Glad we could help. Even after many years in the programming field you can always learn something new. It's the rare exceptions and user issues that we never think of that cause us most problems.
    127,840 pointsBadges:
    report
  • TheRealRaven
    One difficulty is that the UPDATE statement is being built directly into the RUNSQL SQL() parameter. That makes debugging difficult since the value is transient.

    When constructing strins out of parts, try to place the result into a variable like &SQLstmt. Then run the statement as:
    RUNSQL  SQL( &SQLstmt )
    You can then use debug to inspect the actual constructed string as well as to copy/paste that string either into STRSQL for syntax checking or into a forum such as ITKE for others to review.
    33,050 pointsBadges:
    report
  • TheRealRaven
    ...when dealing with SQL.
    Things like "select top 100" vs "fetch first 100 rows only".

    It should be noted that fetch first 100 rows only is "SQL" and that select top 100 is not. See the SQL Standard for details.
    33,050 pointsBadges:
    report
  • ToddN2000
    @Raven:

    When running SQL in SSMS 2008r for example
    to get the first 100 rows you use 

    SELECT TOP 100 
      FROM [database].[dbo].[file]

    but to run SQL on the i-series and get 100 rows it's

    Select * from library/file
    fetch first 100 rows only

    never understood why the difference in the SQL statement structure differences.
    127,840 pointsBadges:
    report
  • TheRealRaven
    @ToddN2000:

    The difference is that one follows the SQL Standard and the other doesn't. See, e.g., topic '7.13 <query expression>' in Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation). (.PDF available here among other sites.)

    The purpose of the standard, of course, is to promote portability. The purpose of being non-standard tends to be to inhibit portability. The FETCH form generally works across compliant DBMSs not just DB/2 for i.
    33,050 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: