[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Another SQL with parm

Posted on 2016-10-24
8
Medium Priority
?
57 Views
Last Modified: 2016-10-24
I am a SQL novice, and I have read the other parm resolutions, to this again asked question of getting a SQL procedure to work with inbound parms.  I get the dreaded SQL0240 @Plant_Parms in *libl type *SQLUDT not found.  I thought this procedure looked pretty good and should work, but the Compiler sees it differently.  Unfortunately I am plowing new ground so resident experts are left scratching their heads.  I would appreciate any help someone can give me.  Thanks so much.

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 C1(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 DISTINCT      
              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
Comment
Question by:Alice T
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 18

Accepted Solution

by:
Dave Ford earned 2000 total points
ID: 41857812
To start with, it looks like you have a comma after @Plant_Parms and before "Char(140)". I'll keep looking into it.

HTH,
DaveSlash
0
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 2000 total points
ID: 41857832
Also, you're inserting into a cursor (C1). I don't believe you can insert into a cursor. You can only insert into a table (and sometimes a view ... although I wouldn't recommend that).

HTH,
DaveSlash
0
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 2000 total points
ID: 41857841
So, did your error occur when you CREATE the stored proc, or does it occur when you RUN the stored proc?

If it occurs when you run it, what command-line do you use to run?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 2000 total points
ID: 41857851
Just out of curiosity, try calling it with only one plant_parm

e.g.
call MM215AP3 ('MyPlantParm');
0
 

Author Comment

by:Alice T
ID: 41857858
All errors were happening when I was creating the procedure.  I have been running everything in Navigator.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41857915
What happens if you leave out DISTINCT from your query?

"select distinct" slows down queries and performing a select distinct over many joins where dollars are involved may in fact be plain wrong (sometimes, I don't know if it is here).

I only mention this because you state you are a SQL novice. Using "select distinct" is NOT always a good option.
0
 

Author Comment

by:Alice T
ID: 41857944
Ok this is last check before I go to sleep.  I am excited about tomorrow.  (I know sad I get excited over programming projects) Thank you all for your advice.  

Yes I am a novice, newbie to pure SQL.  If you have any suggestions on reading material or courses I am open to suggestions.  I am wanting to leave a legacy of programs the next generation can read.

The posted procedure is my second complex SQL procedure. The posted procedure was a piece of the original procedure.  I broke the original up in three pieces to isolate the area of issue.

Good night and thank you again for all the advice.  I freaking needed it.  This project has given me a few more grey hairs, and a couple more wrinkles.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41857949
You are in luck, a great free resource for D2 is
"SQL Cookbook" by Graeme Birchall

There are a great many places where this stored, here is one: (version 9.7)
http://math-cs.gordon.edu/courses/cps352/2015-spring/resources/DB2V97CK.pdf
1

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

656 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