RUNSQLSTM

1590 pts.
Tags:
AS/400
SQL Query
Hi All, My below SQL query is running fine:
- SELECT a.USRP,a.USER1, SUBSTR(a.USER1,23,3) as Template, BRCB, b.MGRP,b.ICDE,INAR,a.SECL,ACA1,ACA2,ACA3,ACA4,ACA5,ACA6 FROM muserdd as a left outer join MACBRDD as b on a.USRP = b.USRP left outer join MITEMDD as c on b.MGRP = c.MGRP and b.ICDE = c.ICDE order by a.USRP
But same is giving error when I'm trying to execute it by RUNSQLSTM by giving my source file and library name and also I have given member type as text and pasted this query in this text type member. My RUNSQLSTM command getting failed any suggestion/idea to make this RUNSQLSTM command successful. However I doubt some syntax error in my Text type source member but not sure how to correct it. However getting error message that positional value can not follow KEYWORD something like that. Please help me by providing the exact correct source which will help to run my RUNSQLSTM command flawlessly.

Software/Hardware used:
AS/400

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 24  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 6r
    Specially CL/400 Experts.
    1,590 pointsBadges:
    report
  • 6r

    Also Techies , who have run RUNSQLSTMT perfectly in AS/400 also please share their suggestions.

    Thanks

    1,590 pointsBadges:
    report
  • TomLiotta

    My RUNSQLSTM command getting failed any suggestion/idea to make this RUNSQLSTM command successful.

    The major suggestion is not to use RUNSQLSTM with a SELECT statement. A SELECT statement makes no sense in that command.

    A SELECT doesn't do anything. All it does is "select" a set of records. You need to select records together with some function such as a Report Writer or in a SQL program that will actually do something with the selected records such as FETCH them for processing. Simply running a SELECT only does the selecting without any processing.

    There is no actual "fix". Instead, you need to determine what your goal is, and then create a process to do it.

    What exactly are you trying to do?

    Tom

    125,585 pointsBadges:
    report
  • 6r

    What my main goal is to make this RUNSQLSTM command successfuly with in a CL program and i will get certain file in a specific location and that needs to be refreshed (or replaced) whenever i call my cl program.

     

    1,590 pointsBadges:
    report
  • TomLiotta

    i will get certain file in a specific location...

    What location are you putting the selected records into?

    Tom

    125,585 pointsBadges:
    report
  • 6r

    since later on i am using CPYTOIMPF command in my cl program which will copy the records which have been obtained from above RUNSQLSTM and then they will be refereshed (replaced) whenever i wish to call my program

    But main obstacle is that RUNSQLSTM command which is failing again and again,however i have pasted sql query as it is in my Source member whose type is TEXT.

    Thanks

     

    1,590 pointsBadges:
    report
  • 6r

    that location is specific folder but that is not at all part of program logic if my RUNSQLSTMT command works my objective will be completed.

     

    Thanks

    1,590 pointsBadges:
    report
  • TomLiotta

    If all you want to do is get the selected rows into a CPYTOIMPF streamfile, then use your SELECT statement in a CREATE VIEW statement.

    Create a VIEW defined as your SELECT. Then use the VIEW for the CPYTOIMPF FROMFILE() parameter. You might use RUNSQLSTM one time to run a CREATE VIEW statement, but it doesn't need to be in a CL program.

    Tom

    125,585 pointsBadges:
    report
  • TomLiotta

    that location is specific folder...

    I hope it's not really a "folder". It should be a directory in the /root file system rather than a "folder".

    Tom

    125,585 pointsBadges:
    report
  • 6r

    So please primarily advise how to make that RUNSQLSTM successful since I'm stuck at this point only.

    SQL query runs perfectly so hoping this text member where I have pasted that

    SQL query should also work using  RUNSQLSTMT but it's not happening likewise I'm expecting. Please advise from this point of  view mainly.

    Thanks

    1,590 pointsBadges:
    report
  • 6r

    I am not using view etc. since after running that SQL query using shift f1(F1)

    have already created a file of the SQL query output and that will be replaced using my CL program each time once my RUNSQLSTM starts working fine.

    And I'm primarily focused to make my RUNSQLSTM command successful and not want to consider other alternatives.

    Thanks

    1,590 pointsBadges:
    report
  • 6r

    I believe there is a certain syntax to write in a member type TEXT.

    Where I just pasted this SQL query where as that should be written in a proper syntax that's the main cause and that I don't know much how to write SQL query in a correct sysntax in member type text which could be easily called from command RUNSQLSTMT. Hope this helps to clarify where I'm stuck exactly.

    Thanks

    1,590 pointsBadges:
    report
  • 6r

    My question is only this much  "the SQL query which I have written needs to be put in a correct syntax in member source type' TEXT', so that it could be easily called using RUNSQLSTMT.

    Thanks

    1,590 pointsBadges:
    report
  • TomLiotta

    i am not usinng view

    I know you're not using a view. A VIEW is just a better way to do your task.

    have already created a file of the sql query output

    I don't know why you'd want to create and maintain a new physical file when a VIEW could be used and would be faster and much more efficient while taking less space on the system. But you can use a physical file.

    Just change your source member to be an INSERT statement. Use your current SELECT statement as the input for the INSERT.

    INSERT INTO yourfile (
       SELECT a.USRP,a.USER1, SUBSTR(a.USER1,23,3) as Template, BRCB, 
       b.MGRP,b.ICDE,INAR,a.SECL,ACA1,ACA2,ACA3,ACA4,ACA5,ACA6 
       FROM muserdd as a left outer join MACBRDD as b on a.USRP = b.USRP 
       left outer join MITEMDD as c on b.MGRP = c.MGRP and b.ICDE = c.ICDE 
       order by a.USRP 
      )

    It's not the best way to do it, but it fits what you're asking for. Put your physical file name in place of "yourfile".

    Tom

    125,585 pointsBadges:
    report
  • TomLiotta

    My question is only this much  “the SQL query which I have written needs to be put in a correct syntax in member source type’ TEXT’, so that it could be easily called using RUNSQLSTMT.

    As I've described, it cannot be done.

    RUNSQLSTM cannot run a SELECT statement. It doesn't make any sense.

    Tom

    125,585 pointsBadges:
    report
  • 6r
    even after i put that "
    INSERT INTO yourfile (
       SELECT a.USRP,a.USER1, SUBSTR(a.USER1,23,3) as Template, BRCB, 
       b.MGRP,b.ICDE,INAR,a.SECL,ACA1,ACA2,ACA3,ACA4,ACA5,ACA6 
       FROM muserdd as a left outer join MACBRDD as b on a.USRP = b.USRP 
       left outer join MITEMDD as c on b.MGRP = c.MGRP and b.ICDE = c.ICDE 
       order by a.USRP 
      )
    "
    my runsqlstmt command getting failed with below message:-
    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  . . . :   Review the messages on the listing.  Either correct the   
      problems or specify a higher severity on the ERRLVL parameter. Try the      
      request again.                                                              
    Technical description . . . . . . . . :   The RUNSQLSTM command found errors  
      with a severity greater than the requested ERRLVL.                          
    1,590 pointsBadges:
    report
  • 6r

    i am getting error while executing runsqlstm command:-

    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  . . . :   Review the messages on the listing.  Either correct the   
      problems or specify a higher severity on the ERRLVL parameter. Try the      
      request again.                                                              
    Technical description . . . . . . . . :   The RUNSQLSTM command found errors  
      with a severity greater than the requested ERRLVL.                          

    1,590 pointsBadges:
    report
  • PGMBOB

    Consider creating an output file or create table with the field defintions. Try to change the select to an "Insert Into" the output file with your statement to select the records.

    Do you get correct results when you run your statement in an interactive STRSQL session?

    Check also RUNSQL command. you can run a statement without the source member.

    Good Luck!

     

    1,110 pointsBadges:
    report
  • 6r

    now runsqlstmt is working fine but that COPYTOIMPF is failing

    looks from file ans to file are of same length tha's the cause but how to increase total record length once the file is created?????

    1,590 pointsBadges:
    report
  • TomLiotta

    i am getting error while executing runsqlstm command:

    What error are you getting? You showed a message that said there was an error, but you didn't show the error message to us. We can't help if you don't show us the error.

    The error is on the listing that was created by RUNSQLSTM.

    Tom

    125,585 pointsBadges:
    report
  • TomLiotta

    how to increase total record length once the file is created?

    You can't increase the length after the file is created. That is, you can change the definition and re-create the file. But that doesn't seem to be what you are asking.

    What does record length have to do with CPYTOIMPF? The command will use the file that you create. It can only use the length that exists. Redefining and recreating the file won't change how CPYTOIMPF works.

    As with RUNSQLSTM, you need to show us the error message if you want help. We can't suggest any fix if we don't know the problem.

    Tom

    125,585 pointsBadges:
    report
  • 6r

    Thanks that RUNSQLSTMT is working fine.

    Bt some issues seems to be with CPYTOIMPF:-

    CPYTOIMPF  FROMFILE(A/File1) +                      
                 TOFILE(A/File2) MBROPT(*REPLACE) +    
                 FROMCCSID(*FILE) TOCCSID(*FILE) +             
                 RCDDLM(*EOR) STRDLM(*NONE) FLDDLM('=')        

     

    is giving error like "Positional value cannot follow keyword parameter."

    What does that mean?

    details are :-

    Message . . . . :   Positional value cannot follow keyword parameter.         
    Cause . . . . . :   All positional values specified (without keywords) must   
      precede all values specified with keywords.  This error can occur if there  
      is a blank between a keyword and the value for the keyword.                 
        This error can also occur when the value keyed into the command line of a 
      list display is not specified with keywords. (A value keyed into a command  
      line will be interpreted as a parameter when an option number has also been 
      keyed into the option field of a list entry.  The parameter may be ignored  
      or it may be used.)                                                         
    Recovery  . . . :   Enter all positional parameters or after specifying a     
      keyword, only use parameter values in the keyword format.                   
        If you are trying to enter a command on a list display command line be    
                                                                           More...  

    sure that all list option fields are empty."

    It was working fine previously but suddenly not sure what happened like

    started getting above error.

    Thanks

     

    1,590 pointsBadges:
    report
  • philpl1jb

    CPYTOIMPF  FROMFILE(A/File1) +                      
                 TOFILE(A/File2) MBROPT(*REPLACE) +    
                 FROMCCSID(*FILE) TOCCSID(*FILE) +             
                 RCDDLM(*EOR) STRDLM(*NONE) FLDDLM(‘=’)        

     

    is giving error like “Positional value cannot follow keyword parameter.”

    The statement as you posted it could not produce this error because you have all parameters preceeded by a keyword (like FROMFILE).  So you must have identified the wrong statement.  The error occurs after you have used a keyword in a cl command and then continue on that command without keywords. 

     

    50,205 pointsBadges:
    report
  • TomLiotta
    I agree with Phil. I copy your command into one of my systems and it comes through fine. You need to copy a couple lines before and after to show us how the CPYTOIMPF fits with the rest of your code. -- Tom
    125,585 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following