RE: First SQL UDF

["Craig Jacobsen" <CraigJacobsen@xxxxxxxxxxx>]

Peter,

No it looks null.

Here is what I'm getting:
If I do:
SELECT                                                      
Substr(Space(7), 1, 7 - Length(Trim(Char(AiOrder)))) Concat 
 Char(AiOrder) FROM PFINVTRN                                
I get:
String Expression 
    1268           
    1268           
    1268           
    1269           
    1269           
    1269           
If I do:
select CharOrder(aiorder) from pfinvtrn
CHARORDER 
     -     
     -     
     -     
     -     
     -     
     -     
     -     
     -     
     -     
     -     
     -     
     -     

Thanks,

Craig

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Peter Dow (ML)
Sent: Monday, October 22, 2007 1:16 PM
To: Midrange Systems Technical Discussion
Subject: Re: First SQL UDF

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
>
>  
>
>