[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Another SQL with parm Part 2

Ok this is great all the advice was noted and the procedure ran great for one Plant.  That was the first test and, then the second test was with a string of plants.  The way I constructed the parm was to bring in one plant or a string of plants.  I tried the following with no success.  When I ran the SQL with the following Where it worked and brought in all parms...

WHERE  MMIHP.IHOBSC = 'C' and MMIHP.IHPLNT in ('SC', 'PC', 'TL', 'MB', 'EW')

Following calls I tried that did not work:
Option 1 - Call MM215AP3 (''SC', 'PC', 'TL', 'MB', 'EW'') SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token SC was not valid. Valid tokens: ) ,. Cause . . . . . :   A syntax error was detected at token SC.  Token SC is not a valid token.  A partial list of valid tokens is ) ,.

Option 2 - Call MM215AP3 ('''SC'', ''PC'', ''TL'', ''MB'', ''EW''') SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure MM215AP3 in *N. Cause . . . . . :   Procedure MM215AP3 in *N was called and has returned one or more result sets. Recovery  . . . :   None.

Option 3 - Call MM215AP3 ('SC, PC, TL, MB, EW') SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure MM215AP3 in *N. Cause . . . . . :   Procedure MM215AP3 in *N was called and has returned one or more result sets. Recovery  . . . :   None.

Thanks so much.... Alice
0
Alice T
Asked:
Alice T
  • 23
  • 17
  • 4
1 Solution
 
tliottaCommented:
It looks like option 2 & 3 both worked, but I'm guessing that they didn't return the results that you expect nor want. Without seeing the code for the proc, there's no way to guess what you want. And without seeing some sample data and an example of your desired output, there's no way to guess what you expect.

Can you give some details to help us understand what you're trying to do?

It seems like you want to pass in a list of values that will somehow be used in an IN() clause, but the first thing that isn't clear is how many values are allowed in your list. Will you always send in exactly 5 values or will the number change? Is it supposed to be a single value with embedded commas (and possibly embedded quotes)?

Please clarify what this SQL is supposed to do and why the results aren't right.
0
 
Alice TAlice in DatalandAuthor Commented:
Sorry I am new to this forum and assumed the title would tie to the original that held the procedure.  Please see the procedure below.

Ok this is great all the advice was noted and the procedure ran great for one Plant.  That was the first test and, then the second test was with a string of plants.  The way I constructed the parm was to bring in one plant or a string of plants.  I tried the following with no success.  When I ran the SQL with the following Where it worked and brought in all parms...

WHERE  MMIHP.IHOBSC = 'C' and MMIHP.IHPLNT in ('SC', 'PC', 'TL', 'MB', 'EW')

Following calls I tried that did not work:
Option 1 - Call MM215AP3 (''SC', 'PC', 'TL', 'MB', 'EW'') SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token SC was not valid. Valid tokens: ) ,. Cause . . . . . :   A syntax error was detected at token SC.  Token SC is not a valid token.  A partial list of valid tokens is ) ,.

Option 2 - Call MM215AP3 ('''SC'', ''PC'', ''TL'', ''MB'', ''EW''') SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure MM215AP3 in *N. Cause . . . . . :   Procedure MM215AP3 in *N was called and has returned one or more result sets. Recovery  . . . :   None.

Option 3 - Call MM215AP3 ('SC, PC, TL, MB, EW') SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure MM215AP3 in *N. Cause . . . . . :   Procedure MM215AP3 in *N was called and has returned one or more result sets. Recovery  . . . :   None.

Thanks so much.... Alice

Procedure that is being called
CREATE PROCEDURE MM215AP3 (In @Plant_Parms Char(140))
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    SPECIFIC MM215AP3
    NOT DETERMINISTIC
    MODIFIES SQL DATA  
    CALLED ON NULL INPUT
    SET OPTION  
        ALWBLK = *ALLREAD ,
        DBGVIEW = *SOURCE ,
        ALWCPYDTA = *YES ,
      COMMIT = *None ,
      CLOSQLCSR = *ENDACTGRP ,  
      DECRESULT = (31, 31, 00) ,
      DFTRDBCOL = *NONE ,
      DLYPRP = *NO ,
      DYNDFTCOL = *NO ,  
      DYNUSRPRF = *USER ,
      SRTSEQ = *HEX ,  
      USRPRF = *OWNER
     Begin
       DECLARE MESSAGE CHAR ( 20 ) ;
       DECLARE C1 CURSOR FOR SELECT * FROM MM215AW ;
       DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET MESSAGE = 'OK' ;
       CLOSE C1 ;
        INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#, BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH,  BKDAY, BKYER, BKTIME)

         SELECT        
              MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ' ' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ' ' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ' ' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ' ' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ' ' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ' ' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ' '
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                  Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                  Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                  Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                  Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)

           WHERE
                     MMIHP.IHOBSC = 'C' and MMIHP.IHPLNT in (@Plant_Parms)

          Order By MMIHP.IHPLNT, MMIHP.IHPN ;
        OPEN C1 ;
          RETURN ;
     END
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Option 1 isn't properly quoted, so you got a an Invalid Token error.

Option 2 is properly quoted to pass a string containing quotes.  This looks the "most right" to me.  

SQL0466 is a successful result.  It is telling you that the SP returned a result set.  Not sure how you are running this SP call (STRSQL maybe?) but I'd guess that whatever interface you are using isn't capable of displaying a result set.  Try using Run SQL Scripts in Navigator.

Option 3 is properly quoted to pass a string that doesn't contain quotes.

Same note as option 2.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Alice TAlice in DatalandAuthor Commented:
Hi Gary.... thank you for responding.  I am learning so much to what I should include in the forum.  I am also learning pure SQL.  The above is my second complex procedure.  A whole different way of thinking outside of RPGLE.  

I did the call command as noted above in Navigator.  That is what I thought the message option 3 meant, but it did not populate the file and it should have.  The SQL runs perfectly if I key the plants in with single quotes.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
OK, I see a likely problem.  I don' think it is going to interpret your parameter string as a list.  To pass a list like this and use in in your WHERE clause, I think you're going to need to build the SQL statement in a variable and execute it as dynamic SQL, something like this (untested code):

       -- Declare variable long enough to hold template plus length of list parameter
       DECLARE sql_stmt VARCHAR(2048) not null default 
			"SELECT        
              MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ' ' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ' ' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ' ' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ' ' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ' ' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ' ' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ' '
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                  Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                  Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                  Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                  Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
				MMIHP.IHOBSC = 'C' and MMIHP.IHPLNT in (" || @Plant_Parms || ") 
           Order By MMIHP.IHPLNT, MMIHP.IHPN"

     -- One-line equivalent to PREPARE  and EXECUTE	
     EXECUTE IMMEDIATE sql_stmt;

Open in new window


Also, omit this line for now - you're hiding any errors that get thrown inside the procedure:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET MESSAGE = 'OK' ;
0
 
Alice TAlice in DatalandAuthor Commented:
Wow thanks Gary.... I will definitely give that a whirl and let you know how it goes.  I am finding out how spoiled I am by RPGLE. I am learning by the baptism by fire.  My greatest thanks.... Alice
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Happy to help.  Post back if you have trouble.
0
 
tliottaCommented:
           WHERE
                     MMIHP.IHOBSC = 'C' and MMIHP.IHPLNT in (@Plant_Parms)

Open in new window

Gary's already covered just about everything needed, so I'll only add some peripheral comments for now.

You mentioned ILE RPG. The SELECT in your stored proc has been compiled by the time you run it, just as RPG would be. So, just like RPG, the single parm value will be handled as a single value -- not as a list of separate and distinct list elements. (The same would be true for compiled CL that attempts to handle a single parm as a multi-valued list of elements.)

So, as Gary said, if you want a single value to act as a list, the SELECT must executed as a dynamic statement rather than compiled. And that means that you would need to supply single-quotes around each element and ensure that commas separate them. Those can be supplied as characters in the parm value or the proc code can insert them wherever needed.

Somewhat alternatively, you could define, say, twenty CHAR(2) parms and specify all of them when calling the proc. The SELECT would then have an IN() that listed the twenty parms instead of the one parm you show above. You'd only need to ensure that valid values were in as many parms as you needed for a given CALL, while also ensuring that the remaining parms all had values that would never have a match (e.g., '*N'). Some of this could be done in the proc code itself.

Beyond that, remember that the more you include in the question, the clearer the problem will be to us and the more likely we'll be able to reply with a solid answer. Also, questions and answers are for all members, including ones who might search for answers next month, next year or later. Once a problem is posted, everything about it should remain within the thread. Pieces won't seem to be missing, and anyone from any time zone around the world will always have the latest pieces.

Keep us updated on problems arising from this thread and on how any answer eventually worked.
0
 
Alice TAlice in DatalandAuthor Commented:
Oh my Gosh you all are totally awesome!!  Thank you.  The parameters are going to be staged and passed by a MS Server programmer to my procedures.  I have given him instructions on how to stage the data with the multiple single quotes and commas.  I really wish I found you all sooner... I feel like I am in the den of lions right now.

I have hit a bit of a bump.  I am getting the following error:
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token SQL_STMT was not valid. Valid tokens: GLOBAL. Cause . . . . . :   A syntax error was detected at token SQL_STMT.  Token SQL_STMT is not a valid token.  A partial list of valid tokens is GLOBAL.  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.

Here is my Code:
SET PATH *LIBL ;
Drop Procedure MM215AP3 ;
CREATE PROCEDURE MM215AP3 (In @Plant_Parms Char(140))
   LANGUAGE SQL
    DYNAMIC RESULT SETS 1
      SPECIFIC MM215AP3
      NOT DETERMINISTIC
      MODIFIES SQL DATA  
      CALLED ON NULL INPUT
      SET OPTION  
        ALWBLK = *ALLREAD ,
        DBGVIEW = *SOURCE ,
        ALWCPYDTA = *YES ,
          COMMIT = *None ,
          CLOSQLCSR = *ENDACTGRP ,  
          DECRESULT = (31, 31, 00) ,
          DFTRDBCOL = *NONE ,
          DLYPRP = *NO ,
          DYNDFTCOL = *NO ,  
          DYNUSRPRF = *USER ,
          SRTSEQ = *HEX ,  
          USRPRF = *OWNER
     Begin
         DECLARE MESSAGE CHAR ( 20 ) ;
       DECLARE C1 CURSOR FOR SELECT * FROM MM215AW ;
       --DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET MESSAGE = 'OK' ;
         CLOSE C1 ;

       -- Declare variable long enough to hold template plus length of list parameter
       DECLARE sql_stmt VARCHAR(2048) not null default
                  "SELECT        
              MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ' ' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ' ' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ' ' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ' ' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ' ' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ' ' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ' '
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                  Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                  Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                  Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                  Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                        MMIHP.IHOBSC = 'C' and MMIHP.IHPLNT in (" || @Plant_Parms || ")
           Order By MMIHP.IHPLNT, MMIHP.IHPN" ;

     -- One-line equivalent to PREPARE  and EXECUTE      
       EXECUTE IMMEDIATE sql_stmt;
       OPEN C1 ;
       RETURN ;
       END
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Doesn't like the syntax of that line.  May not like double quotes I used in example.  If so, then you'll have to use single quotes around exterior and double up single quotes in the interior, just like CL.
0
 
Alice TAlice in DatalandAuthor Commented:
Thanks... I will gave that a whirl and it worked after I changed everything to single quotes.  No double quotes.  I do have one question how does this fit into the Insert part of the SQL?
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
I was just simplifying the example for troubleshooting.  

Once you get the SELECT working, add the rest of your desired SQL code to the sqlstmt.  With problems like this with big compound statements, I try to simplify the statement as much as possible to focus on just the key section so I don't get distracted by little syntax issues elsewhere.
0
 
tliottaCommented:
And be aware the iNav's 'Run SQL Scripts' has a debug option that can often help. By reviewing the value of the sql_stmt variable, you might see things that are otherwise almost invisible.
0
 
Alice TAlice in DatalandAuthor Commented:
Thanks guys.... I am still working on it.  I did change the dynamic statement a little, and got it to create.  Here is the final build....
SET PATH *LIBL ;
     Drop Procedure MM215AP3 ;
     CREATE PROCEDURE MM215AP3 (In @Plant_Parms Char(140))
                                                                                                                                                                   
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA  
               CALLED ON NULL INPUT
               SET OPTION  
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,  
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,  
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER

     Begin
       DECLARE SQL_STMT VARCHAR(2048) default 'INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#, BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH,  BKDAY, BKYER, BKTIME)';
         
            DECLARE C1 CURSOR FOR
              SELECT        
              MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then '  ' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then '  ' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then '  ' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then '  ' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then '  ' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then '  ' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = '   '
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                 Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                 Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                 Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                 Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                  MMIHP.IHOBSC = 'C' and MMIHP.IHPLNT in (" || @Plant_Parms || ")
           Order By MMIHP.IHPLNT, MMIHP.IHPN ;

     -- One-line equivalent to PREPARE  and EXECUTE          
CLOSE C1 ;
OPEN C1 ;        
EXECUTE IMMEDIATE sql_stmt;
       
       RETURN ;
       END

When I call it Call atinneye/mm215AP3 ('''EW'', ''TL'', ''PC''')  I get the falling error and that is where I am at right now.....

SQL0501: Cursor C1 not open.

SQL State: 24501
Vendor Code: -501
Message: [SQL0501] Cursor C1 not open. Cause . . . . . :   The cursor C1 was specified in a FETCH or CLOSE statement, but the cursor is not open. Cursor C1 has one of the following conditions: -- Cursor C1 was never opened. -- The cursor C1 was opened in another program or another call of this program and the program was created with CLOSQLCSR(*ENDPGM). -- The cursor C1 was opened in another module or another call of this module and the module was created with CLOSQLCSR(*ENDMOD). -- The cursor C1 was opened in another call of this program and programs which have run SQL statements have ended and the program was created with CLOSQLCSR(*ENDSQL). -- The cursor C1 was opened in another call of this module and the activation group ended between calls. The module was created with CLOSQLCSR(*ENDACTGRP). -- The cursor was closed by a CLOSE, COMMIT, or ROLLBACK statement. -- The cursor C1 was opened under a transaction which is different than the current transaction. Recovery  . . . :   Do one of the following and precompile again: -- Make certain that cursor C1 is opened in the same program or module call prior to using the cursor in an FETCH or CLOSE statement. -- Specify either CLOSQLCSR(*ENDSQL), CLOSQLCSR(*ENDJOB), or CLOSQLCSR(*ENDACTGRP) when precompiling the application. -- If the cursor was closed by a COMMIT or ROLLBACK, specify HOLD on the COMMIT or ROLLBACK statement to preserve any open cursors, prepared statements, and locks on tables.

Processing ended because the highlighted statement did not complete successfully
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
The error is because you issue a CLOSE before you issue an OPEN.  You can't close a cursor that hasn't been opened yet.

There are lots of ways to approach this task, but assuming that your requirements are:

1) Need to pass in a list of locations,
2) Need to extract data from MMIHP and related tables into MM215AW (I assume "W" means it is a work file)
3) Need to return the contents of MM215AW as a result set to the program that called the SP.
4) And also need the data to also be in MM215AW after all this is done for some reason.  (Do you?)

If so, then you should put the WHOLE QUERY (INSERT with sub-select) in the sqlstmt variable, so you can concatenate you parms in.
Then you should EXECUTE IMMEDIATE your dynamic statement to populate MM215AW.
Then you should open a cursor over the MM215AW table, like you were doing in the previous version of the code, since it now contains data.
Then you should RETURN, which will pass the result set back to the calling program.

But my question is, do you really need this work table?  Do you actually do something with this table later, or are you just trying to return a result set containing the results of this query to the caller - that's kind of what it looks like you're doing.

If that's the case, then you can just create your SELECT statement dynamically, open it as a cursor, and return.  No intermediate work table needed.  The caller will get the results of the SELECT query passed back as a result set.
0
 
Alice TAlice in DatalandAuthor Commented:
Ok here is what happens when I include the whole string.

SET PATH *LIBL ;
     Drop Procedure atinneye/MM215AP3 ;
     CREATE PROCEDURE atinneye/MM215AP3 (In @Plant_Parms Char(140))
                                                                                                                                                                   
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA  
               CALLED ON NULL INPUT
               SET OPTION  
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,  
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,  
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER

     Begin
       DECLARE C1 CURSOR FOR SELECT * FROM MM215AW ;
       DECLARE SQL_STMT VARCHAR(2248) default 'INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#, BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH,  BKDAY, BKYER, BKTIME) ;
         
              SELECT        
              MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ''  '' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ''  '' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ''  '' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ''  '' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ''  '' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ''  '' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ''   ''
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                 Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                 Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                 Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                 Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                  MMIHP.IHOBSC = ''C'' and MMIHP.IHPLNT in (" || @Plant_Parms || ")
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;

     -- One-line equivalent to PREPARE  and EXECUTE          
EXECUTE IMMEDIATE sql_stmt;
       OPEN C1 ;
       RETURN ;
       END

I bolded the VARCHAR that was highlighted.

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token VARCHAR was not valid. Valid tokens: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. Cause . . . . . :   A syntax error was detected at token VARCHAR.  Token VARCHAR is not a valid token.  A partial list of valid tokens is DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.  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 following and try the request again: -- Verify the SQL statement in the area of the token VARCHAR. 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 the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

Processing ended because the highlighted statement did not complete successfully
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Two quick things:

Where did you get 2,248?  I cut and pasted into Word and it counted 2,446, and that is before your @Plant_parms variable has been expanded.  You have to count embedded spaces.

Also, your SQL statement is invalid.  You left a semicolon in the middle after the INSERT clause.
0
 
Alice TAlice in DatalandAuthor Commented:
Ok wow such little things will mess your world up.  I made the changes and I upped the size to 3600'

SET PATH *LIBL ;
     Drop Procedure atinneye/MM215AP3 ;
     CREATE PROCEDURE atinneye/MM215AP3 (In @Plant_Parms Char(140))
                                                                                                                                                                   
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA  
               CALLED ON NULL INPUT
               SET OPTION  
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,  
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,  
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER

     Begin
       DECLARE C1 CURSOR FOR SELECT * FROM MM215AW ;
       DECLARE SQL_STMT VARCHAR(3600) default 'INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#, BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH,  BKDAY, BKYER, BKTIME)
         
              SELECT        
              MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ''  '' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ''  '' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ''  '' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ''  '' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ''  '' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ''  '' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ''   ''
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                 Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                 Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                 Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                 Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                  MMIHP.IHOBSC = ''C'' and MMIHP.IHPLNT in (" || @Plant_Parms || ")
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;

     -- One-line equivalent to PREPARE  and EXECUTE          
EXECUTE IMMEDIATE sql_stmt;
       OPEN C1 ;
       RETURN ;
       END

I basically get the same message----SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token VARCHAR was not valid. Valid tokens: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. Cause . . . . . :   A syntax error was detected at token VARCHAR.  Token VARCHAR is not a valid token.  A partial list of valid tokens is DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.  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 following and try the request again: -- Verify the SQL statement in the area of the token VARCHAR. 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 the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

Processing ended because the highlighted statement did not complete successfully



SET PATH *LIBL ;
     Drop Procedure atinneye/MM215AP3 ;
     CREATE PROCEDURE atinneye/MM215AP3 (In @Plant_Parms Char(140))
                                                                                                                                                                   
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA  
               CALLED ON NULL INPUT
               SET OPTION  
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,  
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,  
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER

     Begin
       DECLARE C1 CURSOR FOR SELECT * FROM MM215AW ;
       DECLARE SQL_STMT VARCHAR(3600) default 'INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#, BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH,  BKDAY, BKYER, BKTIME)
         
              SELECT        
              MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ''  '' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ''  '' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ''  '' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ''  '' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ''  '' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ''  '' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ''   ''
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                 Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                 Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                 Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                 Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                  MMIHP.IHOBSC = ''C'' and MMIHP.IHPLNT in (" || @Plant_Parms || ")
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;

     -- One-line equivalent to PREPARE  and EXECUTE          
EXECUTE IMMEDIATE sql_stmt;
       OPEN C1 ;
       RETURN ;
       END
0
 
Alice TAlice in DatalandAuthor Commented:
I forgot to answer your question....
1) Need to pass in a list of locations,  
Yes, as well as a single location.
2) Need to extract data from MMIHP and related tables into MM215AW (I assume "W" means it is a work file)
     Yes and yes the W is a work file.  A composite of multiple master files.
3) Need to return the contents of MM215AW as a result set to the program that called the SP.
    Yes the contents will be taken out to a MS Server and used by Engineering.
4) And also need the data to also be in MM215AW after all this is done for some reason.  (Do you?)
    Yes we hold this file until the next run occurs.  Then the work file is cleared and repopulated.
0
 
tliottaCommented:
This is not clear:
           WHERE
                  MMIHP.IHOBSC = ''C'' and MMIHP.IHPLNT in (" || @Plant_Parms || ")
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;

Open in new window

Do you actually want double-quotes around @Plant_Parms? I'd expect you'd want just single apostrophes.
0
 
Alice TAlice in DatalandAuthor Commented:
Thanks I will change that and see what happens.
0
 
Alice TAlice in DatalandAuthor Commented:
I thought I was making head way and then I hit the error listed below.  It seemed to work until it got to the Declare Cursor.  Have I concord the creation of the Procedure?  And it needs to be executed with data in the parameter being passed?  Remember I am a novice and this is my first working procedure.

MM215AP3.txt

CPF5813: File QSQLSRC in library QTEMP already exists.
CPF5812: Member MM215AP3 already exists in file QSQLSRC in library QTEMP.
CPF5813: File QSQDSRC in library ATINNEYE already exists.
CPF7302: File QSQDSRC not created in library ATINNEYE.
CPF5812: Member MM215AP3 already exists in file QSQDSRC in library ATINNEYE.
CPF7306: Member MM215AP3 not added to file QSQDSRC in ATINNEYE.
CPC7305: Member MM215AP3 added to file QSQLT00000 in QTEMP.
CZS0607: Module MM215AP3 was created in library QTEMP on 10/30/16 at 17:18:34.
CPC5D07: Program MM215AP3 created in library ATINNEYE.
CPC2191: Object MM215AP3 in QTEMP type *MODULE deleted.

Statement ran successfully   (437 ms)


> DECLARE C1 CURSOR FOR SELECT * FROM MM215AW

SQL State: 42612
Vendor Code: -84
Message: [SQL0084] SQL statement not allowed. Cause . . . . . :   The SQL statement is not allowed for one of the following reasons: -- DECLARE CURSOR, DECLARE STATEMENT, FETCH, OPEN, CLOSE, WHENEVER, PREPARE, EXECUTE, EXECUTE IMMEDIATE, INCLUDE, SELECT INTO, VALUES INTO, DECLARE VARIABLE, DECLARE PROCEDURE, DESCRIBE, GET DIAGNOSTICS, SIGNAL, RESIGNAL, ALLOCATE CURSOR, ASSOCIATE LOCATOR, ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, GET DESCRIPTOR, and SET DESCRIPTOR are not allowed in interactive SQL, dynamic SQL, or when using the RUNSQLSTM command. -- BEGIN DECLARE SECTION and END DECLARE SECTION are not allowed in interactive SQL, in dynamic SQL, in RPG, or in REXX. -- A blocked INSERT statement is not allowed in interactive SQL or dynamic SQL. -- The CONNECT, SET CONNECTION, RELEASE, and DISCONNECT statements are not allowed in dynamic SQL or REXX. CONNECT with constants specified for user ID and password is not allowed in a precompiled program. -- SET ENCRYPTION PASSWORD with constants specified for user ID or hint is not allowed in a precompiled program. -- SELECT and VALUES cannot be issued from an EXECUTE IMMEDIATE statement or the RUNSQLSTM command. -- The SET OPTION statement is only allowed in REXX, in a precompiled program, or in SQL routines.  In a precompiled program, it must be the first SQL statement in the program. -- ALLOCATE CURSOR, ASSOCIATE LOCATORS, DECLARE STATEMENT, DECLARE VARIABLE, DECLARE PROCEDURE, GET DIAGNOSTICS, INCLUDE, SELECT INTO, SET CURRENT DEGREE, SET RESULT SETS, SIGNAL, WHENEVER, blocked INSERT, blocked FETCH, locator statements, and SQL descriptor statements are not allowed in REXX. -- The SET TRANSACTION statement is not allowed when the current connection is to a remote database. -- The SET SESSION AUTHORIZATION statement can only be run in the default activation group.  It is not allowed in REXX, or when using the RUNSQLSTM command. -- The SQL statement specified is not a valid statement on the current release of DB2 for IBM i.  The statement may be valid on a future release of DB2 for IBM i or on some other system's implementation of SQL. Recovery  . . . :   The statement cannot be run in this mode. For a CONNECT or SET ENCRYPTION PASSWORD statement in a precompiled program, specify host variables instead of constants. If in interactive SQL, you may syntax check a statement by setting the statement processing value to *SYN.

Processing ended because the highlighted statement did not complete successfully
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Just move that DECLARE CURSOR up to the top with the other DECLAREs.  DECLARE statements have to be at the top of your code block before other executable code.
0
 
Alice TAlice in DatalandAuthor Commented:
Ok will try that again.... I think when I did that I had another error and that was why I moved it down.
0
 
Alice TAlice in DatalandAuthor Commented:
Wh n you say up top is that above or under the Begin statement?
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
After BEGIN, before any other non-DECLARE statement.
0
 
Alice TAlice in DatalandAuthor Commented:
Yeap.... Moved that declare under the Begin statement and this is the error I got.....

SQL7967: PREPARE of statement QZ9E31BE7AF4563001 completed.
SQL7985: CALL statement complete.
CPI4339: Query options retrieved file QAQQINI in library QTEMP.

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token VARCHAR was not valid. Valid tokens: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. Cause . . . . . :   A syntax error was detected at token VARCHAR.  Token VARCHAR is not a valid token.  A partial list of valid tokens is DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.  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 following and try the request again: -- Verify the SQL statement in the area of the token VARCHAR. 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 the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
0
 
Alice TAlice in DatalandAuthor Commented:
In my many of years programming I have never been so frustrated.
0
 
Alice TAlice in DatalandAuthor Commented:
This is what it looks like now.....
      SET PATH *LIBL ;
     Drop Procedure atinneye/MM215AP3 ;
     CREATE PROCEDURE atinneye/MM215AP3 (In @Plant_Parms Char(140))
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA  
               CALLED ON NULL INPUT
               SET OPTION  
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,  
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,  
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER
 
         Begin
         DECLARE C1 CURSOR FOR SELECT * FROM MM215AW  ;    
         DECLARE VSTMT VARCHAR(3600) not null default  'INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#,       BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH, BKDAY, BKYER, BKTIME)  
       SELECT MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ''  '' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ''  '' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ''  '' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ''  '' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ''  '' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ''  '' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ''   ''
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                 Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                 Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                 Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                 Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                  MMIHP.IHOBSC = ''C'' and MMIHP.IHPLNT in (@Plant_Parms)
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;
  EXECUTE IMMEDIATE VSTMT;
 
 End ;
 OPEN C1 ;
 RETURN  ;
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
You've just got some basic syntax to learn.  Because SQL is often made up of big compound statements, it isn't as good as the RPG compiler at telling you exactly where the problem is.

When I have a problem like this, I start dissecting the procedure, and adding back in one statement at a time until I identify the problem.

Here are the problems I can see:

1) @Plant_Parms isn't being concatenated into your VSTMT like it is supposed to be.  SQL is just going to see that as part of the big literal you've declared.

2) END statement is out of sequence.  Needs to be the last line of code in your procedure.
Your END statement is out of sequence, for one thing.  IT needs to be the last line of code.
0
 
Alice TAlice in DatalandAuthor Commented:
Ok.... Got'cha.  Yeah that is what I did all day today was pull it out, move it around.... And I had the concat bars || in and took them out, I will try them with the END statement at the bottom.  I was suppose to go on vacation tomorrow and looks like I will be working.  :-(
0
 
Alice TAlice in DatalandAuthor Commented:
I feel like Snoopy Aaaaaauuuuggghhhh!!

The bolded area is what was highlighted.

SQL7967: PREPARE of statement QZ9E31C502CCCA9001 completed.
SQL7985: CALL statement complete.
CPI4339: Query options retrieved file QAQQINI in library QTEMP.

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token VARCHAR was not valid. Valid tokens: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. Cause . . . . . :   A syntax error was detected at token VARCHAR.  Token VARCHAR is not a valid token.  A partial list of valid tokens is DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.  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 following and try the request again: -- Verify the SQL statement in the area of the token VARCHAR. 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 the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.



SET PATH *LIBL ;
Drop Procedure atinneye/MM215AP3 ;
CREATE PROCEDURE atinneye/MM215AP3 (In @Plant_Parms Char(140))
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA  
               CALLED ON NULL INPUT
               SET OPTION  
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,  
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,  
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER
 
         Begin
         DECLARE C1 CURSOR FOR SELECT * FROM MM215AW  ;    
         DECLARE VSTMT VARCHAR(3600) not null default  'INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#,       BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH, BKDAY, BKYER, BKTIME)  
       SELECT MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ''  '' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ''  '' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ''  '' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ''  '' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ''  '' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ''  '' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ''   ''
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                 Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                 Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                 Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                 Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                  MMIHP.IHOBSC = ''C'' and MMIHP.IHPLNT in ( || @Plant_Parms || )
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;
  EXECUTE IMMEDIATE VSTMT;
 OPEN C1 ;
 RETURN  ;
END ;
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Sorry to hear that.  

This is one of those cases where instead of spending days banging your head it would have made economic sense to just bring in an expert for an hour or two.  After that, you have a working example to build off of instead of trying to create from scratch yourself while still a novice.  Takes most people 1,000 - 2,000 hours of experience to develop production-level proficiency in a new language.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Alice, do you know CL?
0
 
Alice TAlice in DatalandAuthor Commented:
Yes I do.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
OK, the string syntax on the vstmt default is just like CL.  You need to close the first string, then concatenate, then open a new string:

... and MMIHP.IHPLNT in (' || @Plant_Parms || ')
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;

SQL compiler can't tell where this string ends.  VARCHAR definition includes this default string, which is why it keeps telling you that VARCHAR is invalid.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
One more thought:  I'm not sure that you can use a variable like this in the default.  

If you keep having trouble, just declare the variable, but don't set the default.  Then use the SET statement to assign the value to the variable.

You can define a constant value for a default, but I've never tried to concatenate a parm in a default value for an SQL  variable:

 DECLARE VSTMT VARCHAR(3600);
SET VSTMT = 'INSERT ...
0
 
Alice TAlice in DatalandAuthor Commented:
YEah I am still getting that crummy error.... and it highlighting ~~~VARCHAR(3600)~~

I did the set and remved the Default earlier today I will try again with some of the changes I have made tonight.
0
 
Alice TAlice in DatalandAuthor Commented:
This becoming silly.... or I am getting punchy....  All the "Experts" at my place are still scratching their heads.  All I have heard is she's doing stuff I have never done.  I want to program for another 10 to 15 years so I want to learn the new stuff.

Is that VARCHAR too high?  It is dying on the VARCHAR(3600)  I underlined part of the message.  What does the underlined info mean?  Am I missing something in the declare for this type of use?


SQL7967: PREPARE of statement QZ9E31CBE7304EA001 completed.
SQL7985: CALL statement complete.
CPI4339: Query options retrieved file QAQQINI in library QTEMP.

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token VARCHAR was not valid. Valid tokens: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. Cause . . . . . :   A syntax error was detected at token VARCHAR.  Token VARCHAR is not a valid token.  A partial list of valid tokens is DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.  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 following and try the request again: -- Verify the SQL statement in the area of the token VARCHAR. 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 the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

Here is a fresh copy of the Procedure....

SET PATH *LIBL ;
     Drop Procedure atinneye/MM215AP3 ;
     CREATE PROCEDURE atinneye/MM215AP3 (In @Plant_Parms Char(140))
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA  
               CALLED ON NULL INPUT
               SET OPTION  
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,  
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,  
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER
 
         Begin
         DECLARE C1 CURSOR FOR SELECT * FROM MM215AW  ;    
         DECLARE VSTMT VARCHAR(3600) ;
         Set VSTMT = 'INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#,       BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH, BKDAY, BKYER, BKTIME)  
       SELECT MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ''  '' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ''  '' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ''  '' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ''  '' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,  
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ''  '' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ''  '' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP  
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ''   ''
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                 Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                 Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                 Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                 Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                  MMIHP.IHOBSC = ''C'' and MMIHP.IHPLNT in (' || @Plant_Parms || ')
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;
  EXECUTE IMMEDIATE VSTMT;
 OPEN C1 ;
 RETURN  ;
END ;
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
You just had variable and cursor declaration sequence reversed.  Here is a great book for learning IBM i DB2 SP programming:

http://www.redbooks.ibm.com/abstracts/sg248326.html?Open

On page 19, Example 2-22 - Structure shows the structure of a compound SQL statement:

BEGIN <atomic setting>
<variable declarations>
<cursor declarations>
<condition handler declarations>
<SQL statement list/procedure logic>
END

I created this and it worked:

    CREATE or REPLACE PROCEDURE garyp.MM215AP3 (In @Plant_Parms Char(140))
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA  
               CALLED ON NULL INPUT
               SET OPTION  
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,  
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,  
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER
 
         Begin
         DECLARE VSTMT VARCHAR(3600) ;
         DECLARE C1 CURSOR FOR SELECT * FROM garyp.test  ;    
         Set VSTMT = 'INSERT INTO garyp.test (fld1, fld2) SELECT left(UPSYST,2), UPUPRF FROM garyp.dspusrprf where upuprf in (' || @Plant_Parms || ')';
         EXECUTE IMMEDIATE VSTMT;
         OPEN C1 ;
         RETURN  ;
END

Tested using:

call garyp.MM215AP3 ('''USER1''');

-and-

call garyp.MM215AP3 ('''USER1'',''USER2''');
0
 
Alice TAlice in DatalandAuthor Commented:
Thanks so much Gary.  I will give that a whirl and let you know what I end up with.
0
 
Alice TAlice in DatalandAuthor Commented:
Thank you for everyone's help.... this is my final copy.  Special thanks to Gary for your steady help.

--SET PATH *LIBL ;
     --Drop Procedure atinneye/MM215AP3 ;
     CREATE OR REPLACE PROCEDURE atinneye/MM215AP3 (In @Plant_Parms Char(142))
               LANGUAGE SQL
               DYNAMIC RESULT SETS 1
               SPECIFIC MM215AP3
               NOT DETERMINISTIC
               MODIFIES SQL DATA
               CALLED ON NULL INPUT
               SET OPTION
               ALWBLK = *ALLREAD ,
               DBGVIEW = *SOURCE ,
               ALWCPYDTA = *YES ,
               COMMIT = *None ,
               CLOSQLCSR = *ENDACTGRP ,
               DECRESULT = (31, 31, 00) ,
               DFTRDBCOL = *NONE ,
               DLYPRP = *NO ,
               DYNDFTCOL = *NO ,
               DYNUSRPRF = *USER ,
               SRTSEQ = *HEX ,                                    
               USRPRF = *OWNER
 
         Begin
                        DECLARE VSTMT VARCHAR(3600) ;
         DECLARE C1 CURSOR FOR SELECT * FROM MM215AW  ;  
         Set VSTMT = 'INSERT INTO MM215AW(BKPLNT, BKPN, BKDES, BKCOMC, BKCCDES, BKCOMS, BKCSDES, BKUOM, BKVNPN, BKVND#,       BKVDRNAM, BKQOH, BKLDTIM, BKBUYR, BKBUYRNM, BKCURCOST, BKSTDCOST, BKITMSTS, BKAVGDUSG, BKHAZMAT, BKMTH, BKDAY, BKYER, BKTIME)
       SELECT MMIHP.IHPLNT, MMIHP.IHPN,
              Case When MMIMP.IMDES is NULL Then ''  '' Else MMIMP.IMDES End,
              MMIHP.IHCOMC, MM215ICW.BDCCDES, MMIHP.IHCOMS,  MM215ISW.BESCDES,
              Case when MMIMP.IMIUOM is Null Then ''  '' Else MMIMP.IMIUOM End,
              Case When MMIVP.IVVPN is Null Then ''  '' else MMIVP.IVVPN End,
               MMIHP.IHVEND,
               Case When VENDMAST.Name is Null Then ''  '' Else VENDMAST.Name End,
               Case When MMIDP.IDQOH is NULL Then 0 Else MMIDP.IDQOH END,
               Case When MMIHP.IHLEAD is Null Then 0 Else MMIHP.IHLEAD End,
               MMIHP.IHBUYR, MMBYP.BYDNME,
               Case When MMIVP.ivcst$ is Null Then 0 Else cast(ivcst$/(10**ivdecp) as dec(9,3)) End,
               Case When MMIHP.IHCST$ is NULL Then Cast(0 as dec) Else MMIHP.IHCST$ End,
               Case When MMIHP.IHOBSC is NULL Then ''  '' Else MMIHP.IHOBSC End,
               Case When MMIHP.IHADU is NULL Then 0 Else MMIHP.IHADU END,
               Case When MMIHP.IHHAZD is NULL Then ''  '' Else MMIHP.IHHAZD End,
               Case When MMIVP.IVMTH is NULL then 0 Else MMIVP.IVMTH End,
               Case When MMIVP.IVDAY is Null then 0 Else MMIVP.IVDAY End,
               Case When MMIVP.IVYER is NULL then 0 Else MMIVP.IVYER End,
               Case When MMIVP.IVTIME is NULL then 0 Else MMIVP.IVTIME End
           FROM MMIHP
                 Left Join MMIDP on MMIDP.IDPLNT = MMIHP.IHPLNT and MMIDP.IDPN = MMIHP.IHPN and MMIDP.IDLOCN = ''   ''
                 Left Join MMIMP on MMIMP.IMPN = MMIHP.IHPN
                 Left Join MMIVP on (MMIVP.IVPLNT = MMIHP.IHPLNT and MMIVP.IVPN = MMIHP.IHPN and MMIVP.IVVEND = MMIHP.IHVEND)
                 Left Join VENDMAST on (VENDMAST.VENDNO = MMIVP.IVVEND)
                 Left Join MMBYP on (MMBYP.BYBYID = MMIHP.IHBUYR)
                 Left Join MM215ICW on (MM215ICW.BDCOMC = MMIHP.IHCOMC)
                 Left Join MM215ISW on (MM215ISW.BESCOCD = MMIHP.IHCOMC and MM215ISW.BESBCOCD = MMIHP.IHCOMS)
           WHERE
                 MMIHP.IHPLNT in (' || @Plant_Parms || ') and  MMIHP.IHOBSC = ''C''
           Order By MMIHP.IHPLNT, MMIHP.IHPN' ;
  EXECUTE IMMEDIATE VSTMT;
OPEN C1 ;
RETURN  ;
END
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Now to make this production-quality code, it needs error handling and logging - and of course you need to build any necessary index package.   To get a list of optimizer access plan decisions at runtime, do this:

From green-screen

STRDBG
STRSQL
Execute your SELECT statement
F3
DSPJOBLOG

From Navigator / Run SQL Scripts

Enable: Options - "Include Debug Messages In Joblog"

Optimizer will log messages in debug mode telling you what method it selected for each table.  For this query, with what I assume is relatively low selectivity on each table, you'll want to see index probes.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Valuable references if you're new to IBM i DB2 SQL Stored Procedures:

The best “Stored Procedures 101” book is the 2016 Stored Procedures, Triggers, and Functions on IBM DB2 for i Redbook.  If you don’t do any other reading on SQL procedures, skim this book.  Loaded with examples and practical advice, and also explains some complicated topics like the difference between how the library list and current library are handled when using *SQL naming vs *SYS naming.

http://www.redbooks.ibm.com/redbooks/pdfs/sg248326.pdf

I've provided Version 7.3 (V7R3) links below.  You should search for the document title with your OS version to get features that apply to your environment.

IBM i Version 7.3 Database DB2 for i SQL Reference is the core manual with syntax diagrams and examples of most statements:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzpdf.pdf

IBM i Version 7.3 Database Performance and Query Optimization
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqpdf.pdf
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 23
  • 17
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now