Re: First SQL UDF

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