Re: First SQL UDF

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

Hi Craig,

The problem I see with your UDF is that it is accepting an 8-digit 
number and returning a 7-character field, but your "set" statement

set alpha = 
Substr(Space(7), 1, 7 - Length(Trim(Char(numb)))) Concat 
Char(numb);

if passed 1268, would return 3 spaces concatenated with bbbb1268, where b = blank. The "set" command would left adjust that into field "alpha" so all you would see is blanks.  If you add the "Trim" to the 2nd "Char(numb)", you should get the expected result of bbb1268.

Maybe the null result is because the SET command doesn't want to truncate the result.

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




Craig Jacobsen wrote:
> 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
> >
> >  
> >
> >   
> >