We help IT Professionals succeed at work.

interactive SQL can not find stored procedure

iskibinska
iskibinska asked
on
Hi I created a function :

SET CURRENT SCHEMA DEFAULT;            
CREATE  FUNCTION CENA                  
                 (PRODPRC DECIMAL(11,2),
                  DSCPRC DECIMAL(5,2),  
                  PRDQTY DECIMAL(5,2),  
                  CORP CHAR(2),        
                  ACCT1 CHAR(7),        
                  ACCT2 CHAR(4),        
                  LIB CHAR(10))        
  RETURNS DECIMAL (11,2)                
                                       
  LANGUAGE SQL                          
  SPECIFIC CENA                        
  READS SQL DATA                        
                                       
  BEGIN                                
 ...
 END;
ran RUNSQLSTM on it and  CENA *SRVPGM was created in QGPL . However when
I am trying to run interactive SQL:
SELECT  customer,cena(price,qty, disc, customer),
'E2DISPDTFL')                                                  
   from file1                                        

I am getting the message:
CENA in *LIBL type *N not found.

I have another function which is called without any problems        


Comment
Watch Question

Dave FordSoftware Developer / Database Administrator

Commented:
It looks like the function takes more parameters thans you're supplying.

HTH,
DaveSlash

Author

Commented:
i am passing all 7 parameters, i put a shortcut
Dave FordSoftware Developer / Database Administrator

Commented:

So, what is the ACTUAL select statement that uses the function?

Remember that the number of parameters and data-type of the parameters are considered the "signature" of the function. So, it you pass in a different number of parameters or parameters with different data-types, it will throw the error you see.

Author

Commented:
ok, i fixed all the parameters, whenever i have packed i put decimal , so it is like this:

 CREATE  FUNCTION CENA                  
                 (PRODPRC DECIMAL(7,2),
                  DSCPRC DECIMAL(5,2),
                  PRDQTY INTEGER,      
                  CORP CHAR(2),        
                  ACCT1 CHAR(7),      
                  ACCT2 CHAR(4),      
                  LIB CHAR(10))        



my call:


SELECT  cena(price,disq,QTY,corp,acct1,act2,
'librarynme')                                          
   from ORDERDTL                                        

and

price is 7.2 packed
disc is 5.2 packed
qty is 7.0 packed
corp is char(2)
acct1 is char(7)
acct2 is char(7)
 

and still getting the error
VP Technology / Senior Consultant
Commented:
You got data type and length issues.  Error message it telling you that it can't find a function with that name with matching data types.  Data types and lenghts passed need to match data types declared:

PRDQTY is integer, qty is packed - Declare PRDQTY as packed or cast qty to INT on the function call

ACCT2 is char(4), acct2 is char(7) - I think this is just a typo.  Makes sure they match, though.

LIB is declared as CHAR(10).  Character literals get passed by default as VARCHAR.  If you plan to pass LIB as a literal, either declare the parm as VARCHAR(10), or cast to CHAR on the call:

CENA(price,disc,qty,corp, acct1,acct2,CAST('LIBRARYNAME' as CHAR(10)))
Assuming your list of column types and lengths is correct, and you plan on passing a literal for library, this should do it.  I think you made a mistake when you said acct is char(7), so just make sure it is consistent with the field definition that you plan to pass.

- Gary Patterson

PS - If you still have problems after correcting your data types, it is probably a library list or naming format issue.  Qualify the call:

QGPL.CENA or QGPL/CENA


CREATE  FUNCTION CENA                  
                 (PRODPRC DECIMAL(7,2),
                  DSCPRC DECIMAL(5,2),
                  PRDQTY DECIMAL(5,0),      
                  CORP CHAR(2),        
                  ACCT1 CHAR(7),      
                  ACCT2 CHAR(4),      
                  LIB VARCHAR(10))    

Invoke: CENA(price,disc,qty,corp,acct1,acct2,'LIBRARYNAME')    

Open in new window