Solved

Another SQL with parm Part 2

Posted on 2016-10-25
44
49 Views
Last Modified: 2016-11-01
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
Comment
Question by:Alice T
  • 23
  • 17
  • 4
44 Comments
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Happy to help.  Post back if you have trouble.
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
           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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
Thanks I will change that and see what happens.
0
 

Author Comment

by:Alice T
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
Ok will try that again.... I think when I did that I had another error and that was why I moved it down.
0
 

Author Comment

by:Alice T
Comment Utility
Wh n you say up top is that above or under the Begin statement?
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
After BEGIN, before any other non-DECLARE statement.
0
 

Author Comment

by:Alice T
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
In my many of years programming I have never been so frustrated.
0
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Alice, do you know CL?
0
 

Author Comment

by:Alice T
Comment Utility
Yes I do.
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 

Author Comment

by:Alice T
Comment Utility
Thanks so much Gary.  I will give that a whirl and let you know what I end up with.
0
 

Author Comment

by:Alice T
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now