?
Solved

Another SQL with parm

Posted on 2016-10-24
8
Medium Priority
?
46 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
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

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