Solved

Another SQL with parm

Posted on 2016-10-24
8
17 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 18

Accepted Solution

by:
daveslash earned 500 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:daveslash
daveslash earned 500 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:daveslash
daveslash earned 500 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
 
LVL 18

Assisted Solution

by:daveslash
daveslash earned 500 total points
ID: 41857851
Just out of curiosity, try calling it with only one plant_parm

e.g.
call MM215AP3 ('MyPlantParm');
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 48

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 48

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

23 Experts available now in Live!

Get 1:1 Help Now