SQLstate 42704 "Can't find UDF"
Last Post 06 Mar 2013 12:20 AM by B.Hauser. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Rex Reese
New Member
New Member
Posts:44

--
01 Mar 2013 03:13 PM

from the green screen I executed a STRSQL and created the following function:

 

create function qgpl/getedition(inisbn integer) returns char(6)    

language sql                                                       
begin                                                              
Return (                                                           
select                                                             
case                                                               
  when crnted < 10 then concat('20000',cast(crnted as char(4)))    
  when crnted <=13 and crnted >= 10                                
           then concat('2000',cast(crnted as char(4)))             
  when crnted < 1000  then concat('200',cast(crnted as char(4)))   
  when crnted >= 5300 then concat('19',cast(crnted as char(4)))    
  when crnted < 5300  then concat('20',cast(crnted as char(4)))    
Else   char(crnted)                                                
End                                                                
from bkmast where isbn18 = inisbn );                               
End    

 

The function creates normally in library QGPL

I then created a binding directory and added the resulting *SRVPGM as a binding directory entry.

I then created and compiled the following pgm:

H dftactgrp(*no) actgrp(*caller)                               
H bnddir('MYTEST')                                             
                                                               
d myedition       s              6a                            
d myisbn          s             18p 0                          
D ok              C                   '00000'                  
                                                               
 /free                                                         
  myisbn = 978047137137;                                       
  exec sql                                                     
   select getedition(:myisbn)                                  
   into :myedition                                             
   from bkmast;                                                
                                                               
  If sqlstt <> ok;                                             
    myedition = 'ERROR';                                       
  Endif;                                                       
                                                               
  dsply myedition;                                             
  *InLR = *On;                                                 
 /end-free



However when the program runs, I get a SQLSTAT = '42704' which says:
"An undefined object or constraint name was detected."


What am I missing?
                                                    
B.Hauser
Veteran Member
Veteran Member
Posts:2056

--
02 Mar 2013 02:54 AM
Your UDF expects an Integer value, but in your RPG program you are passing a packed numeric value.

SQL functions can be overloaded, that means the multiple functions with the same name but different parameter definition can coexist within the same schema/library.

That means a function with a packed parameter is searched, but not found! Convert either your function to expect a packed numeric parameter or define your host variable as Integer or convert your packed data into an integer value before calling the function.

BTW you do neither need a binding directory nor insert the UDF into any binding directory nor specify the UDF service program directly within the compile command. SQL functions are searched and called dynamically.

 But why using an UDF, why not simply creating a view with an additional columns with the case clause?

Something like this:

 Create View YourSchema/YourView as
select a.*,                                                             
case when crnted < 10 then concat('20000',cast(crnted as char(4)))    
  when crnted <=13 and crnted >= 10                                
           then concat('2000',cast(crnted as char(4)))             
  when crnted < 1000  then concat('200',cast(crnted as char(4)))   
  when crnted >= 5300 then concat('19',cast(crnted as char(4)))    
  when crnted < 5300  then concat('20',cast(crnted as char(4)))    
Else   char(crnted)                                                
End NewColumn                                                               
from bkmast a;

This view can be called as follows:

 Select NewColumn
from YourView
Where isbn18 = YourValue;

Or within an embedded SQL Command:

Exec SQL Select NewColumn into :YourRtnVar
From YourView
Where isbn18 = :YourHostVar;

Birgitta

Rex Reese
New Member
New Member
Posts:44

--
04 Mar 2013 08:35 AM

Thanks Birgitta,

Good catch on the parameter mismatch.  

I also like your idea for the new view.  I will consider doing that.

 

thanks again

rex

Lynne Noll
Senior Member
Senior Member
Posts:6567

--
05 Mar 2013 02:36 PM
Generally, if you define the parameter as numeric (zoned), it will adjust packed to match, but it won't turn zoned into packed.
There are rules for what will convert; they are peculiar. For example, varying will convert to fixed character but not fixed character to varying (in functions, not stored procedures.)

B.Hauser
Veteran Member
Veteran Member
Posts:2056

--
06 Mar 2013 12:20 AM
Posted By Lynne Noll on 05 Mar 2013 02:36 PM
Generally, if you define the parameter as numeric (zoned), it will adjust packed to match, but it won't turn zoned into packed.
There are rules for what will convert; they are peculiar. For example, varying will convert to fixed character but not fixed character to varying (in functions, not stored procedures.)


The easiest way to get arround is to either use the same data types or explicitly casting the parameter when calling the function.

Birgitta

You are not authorized to post a reply.

Acceptable Use Policy