• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

Another SQL with parm

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
Alice T
Asked:
Alice T
  • 4
  • 2
  • 2
4 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Dave FordSoftware Developer / Database AdministratorCommented:
Just out of curiosity, try calling it with only one plant_parm

e.g.
call MM215AP3 ('MyPlantParm');
0
 
Alice TAlice in DatalandAuthor Commented:
All errors were happening when I was creating the procedure.  I have been running everything in Navigator.
0
 
PortletPaulCommented:
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
 
Alice TAlice in DatalandAuthor Commented:
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
 
PortletPaulCommented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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