my first UDTF error SQL0204
Last Post 04 Dec 2012 12:23 PM by Lynne Noll. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
gio.cot
Basic Member
Basic Member
Posts:285

--
28 Nov 2012 01:06 PM
Hi all
i'm try to create my first UDTF

here my prototype program

 

d Aot01a          pr                  Extpgm('AOT01A')             
D pI_k1Ma1                            Like(k1Ma1) Const            
D pO_k1Aai                            Like(k1Aai)                  
D pO_k1Nri                            Like(k1Nri)                  
D pO_k1Dtr                            Like(k1Dtr)                  
D pO_k1Dtd                            Like(k1Dtd)                  
D pO_k1Nrd                            Like(k1Nrd)                  
D pO_k1Mst                            Like(k1Mst)                  
D pO_k1Cto                            Like(k1Cto)                  
D pI_k1Ma1_N                     5i 0             Const            
D pO_k1Aai_N                     5i 0                              
D pO_k1Nri_N                     5i 0                              
D pO_k1Dtr_N                     5i 0                              
D pO_k1Dtd_N                     5i 0                              
D pO_k1Nrd_N                     5i 0                              
D pO_k1Mst_N                     5i 0                              
D pO_k1Cto_N                     5i 0                              
D pO_SqlStt                      5a                                
D pO_Function                  517a               varying Const    
D pO_Specific                  128a               varying Const    
D pO_ErrorMsg                   70a               varying          
D pI_CallType                   10i 0             Const

and here my script to create UDTF :

Create Function AOT01A_U ( K1Ma1  Char(3))    
   Returns Table                               
 (                                             
 K1AAI  Numeric(2, 0),                         
 K1NRI  Numeric(7, 0),                         
 K1Dtr  Numeric(6, 0),                         
 K1Dtd  Numeric(6, 0),                         
 K1Nrd  Numeric(5, 0),                         
 K1Mst  Numeric(3, 0),                         
 K1Cto  Numeric(6, 0)                          
 )                                             
 external name 'MYLIB/AOT01A'                  
 language rpgle                                
 parameter style db2sql                        
 no sql                                        
 not deterministic                             
 disallow parallel;

i have compiled my RPG program and run the RUNSQLSTM command to created the UDFT; if i go with navigator in QGPL i see my function; but when try from sql to run "select * from table(qgpl/aot01a_u('431')) as x " i receive the error "AOT01A_U in QGPL type *N not found . where i'm wrong ?

thanks in advance

p.s. the ouput numeric fields in my rpg are all signed fields .. is correct "Numeric(x, z)" in my script ? 

 

 

 

 

Ringer
Veteran Member
Veteran Member
Posts:1768
Avatar

--
28 Nov 2012 01:55 PM
SQL treats literals like VARCHAR. Your definition is CHAR. So SQL is not finding a matching signature for the requested Function.

I'd suggest changing your RPG to use VARYING instead and the Function to VARCHAR.

For the RPG input parms, I usually specify VARYING and DECIMAL. Why? Because SQL happily casts Char to VarChar, Int to Decimal automagically when needed. And SQL treats literal strings as VARCHAR and literal integers as type Integer. This lets SQL do the casting instead of the programmer.

And SQL Numeric is Zoned Numeric in RPG. SQL Decimal is Packed Decimal in RPG.

Chris Ringer
gio.cot
Basic Member
Basic Member
Posts:285

--
28 Nov 2012 04:15 PM

Hi  Ringer

thanks for your reply, i have done this  i'd suggest changing your RPG to use VARYING instead and the Function to VARCHAR.  and now it's run but i have another problem : in the parameter "pI_CallType" i receive alwais -1, i expected the first time -1 then 0 (for the details) and at the and 1 . I'm missing something ??

i have followed the example from Scott Klement document "RPG User Defined Functions (UDFs)"

 

Thanks

Ringer
Veteran Member
Veteran Member
Posts:1768
Avatar

--
29 Nov 2012 08:11 AM
I would say yes you are missing something. But hard to say what since we can't see your source code. Can you post it?

Chris Ringer
RichardHart
New Member
New Member
Posts:4

--
29 Nov 2012 11:22 PM
Okay, sorry if I am stating the obvious, but I am not sure you can run a User-defined Table function from greenScreen (that is, from the STRSQL interface).  Or were you running it from Navigator?

Just a thought! 
gio.cot
Basic Member
Basic Member
Posts:285

--
30 Nov 2012 02:01 AM

Hi Chris

i found the problem, i forgot to insert return in the program 

Select;                            
      When pI_CallType = CALL_OPEN;    
        pri_Init();                  
        pri_CloseSql();                
        pri_InitSql();                 
        pri_Declare();                 
        pri_Prepare();                 
        pri_OpenSql();                 
      When pI_CallType = CALL_FETCH;   
        pri_FetchSql();                
      When pI_CallType = CALL_CLOSE;   
        pri_CloseSql();                
        ExSr $Exit;                    
    EndSl;                             
                                       
    Return;                           

Now it's work

 

thanks


 

Lynne Noll
Senior Member
Senior Member
Posts:6567

--
04 Dec 2012 12:23 PM
Just for general information, you can run a table function most anywhere you can run SQL, including interactive SQL and Navigator, and you can put them in a CTE expression, such as a with clause.



You are not authorized to post a reply.

Acceptable Use Policy