Re: First SQL UDF

["Peter Dow (ML)" <maillist@xxxxxxxxxxxxxxx>]

Hi Craig,

How about

set alpha =                                              

Substr(Space(7), 1, 7 - Length(Trim(Char(numb)))) Concat 

Trim(Char(numb));


I'm assuming that when you said it returns null values, that it's actually returning blanks.

hth,
Peter Dow
Dow Software Services, Inc.
909 793-9050




Craig Jacobsen wrote:
> I am trying to write my first SQL UDF converting a numeric value that is 8.0
> to an alpha value that is 7 long right justified with no leading zeros.
>
>  
>
> The code currently used to tie files together in RPG is:
>
> EvalR Work7 = %EditC(AiOrder:'Z')
>
>  
>
> In SQL I am using:
>
> Substr(Space(7), 1, 7 - Length(Trim(Char(AiOrder)))) Concat
>
>  Char(AiOrder) 
>
>  
>
> I tried creating a UDF to accomplish the above to shorten internal SQL Code.
>
> Obviously I am doing something wrong.  The Function does get created but
> when I try to run, all I get is null values.
>
>  
>
>  
>
> Create function CharOrder                      
>
> (numb DEC)                                               
>
> Returns char                                             
>
> language SQL                                             
>
> returns null on null input                               
>
> begin                                                    
>
> declare numb dec(8);                                     
>
> declare alpha char(7);                                   
>
> set alpha =                                              
>
> Substr(Space(7), 1, 7 - Length(Trim(Char(numb)))) Concat 
>
>  Char(numb);                                             
>
> Return alpha;                                            
>
> End                                                      
>
>  
>
>  
>
> TIA,
>
>  
>
> Craig
>
>  
>
>