Function in Function 1

[saintedmunds]

Hi
I have the below UDF that im trying to use another UDF in it. which is also below.

CREATE FUNCTION "fnAverageSymbols"
(
@Symbol1 varchar(1),
@Symbol2 varchar(1),
@Symbol3 varchar(1)
)

RETURNS varchar(1)
AS
BEGIN
Declare @AverageSymbol int, @fnAverageSymbol varcha(1)

Set @AverageSymbol = [ fnChangeSymbolToNumber] (@Symbol1) + [fnChangeSymbolToNumber](@Symbol2) + [fnChangeSymbolToNumber](@Symbol3)

set @AverageSymbol = @AverageSymbol / 3

set @fnAverageSymbol = Case @AverageSymbol
When 1 then 'A'
When 2 then 'B'
When 3 then 'C'
When 4 then 'D'
When 5 then 'E'
Else 'N'
End


RETURN @fnAverageSymbol
END

Using this one

ALTER FUNCTION dbo.fnChangeSymbolToNumber
(@Symbol varchar(1))

RETURNS int
AS
BEGIN
Declare @SymbolNumber int


set @SymbolNumber = Case @Symbol
When 'A' then 1
When 'B' then 2
When 'C' then 3
When 'D' then 4
When 'E' then 5
Else 0
End


RETURN @SymbolNumber
END


So im getting 3 symbols range A - E and i need to find the average so im converting them to numbers dividing by 3 the average and then trying to get the relavant symbol but having a bit of trouble.

Any help would be great

THank you

 

[bborissov]

How about:

CREATE FUNCTION "fnAverageSymbols"
(
@Symbol1  varchar(1),
@Symbol2 varchar(1),
@Symbol3 varchar(1)
)

RETURNS varchar(1)
AS
 BEGIN
      Declare @AverageSymbol int,
              @fnAverageSymbol varcha(1)

      Set @AverageSymbol = ASCII(@Symbol1) + 
                           ASCII(@Symbol2) +
                           ASCII(@Symbol3)

      set @AverageSymbol = @AverageSymbol / 3

      set @fnAverageSymbol  =  CHAR(@AverageSymbol)

RETURN @fnAverageSymbol
END

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

I see 2 problems.

You miss-spelled varchar

[tt][blue]Declare @AverageSymbol int, @fnAverageSymbol varcha[!]r[/!](1)[/blue][/tt]

Also, when calling a UDF, you need to preface it with the owner of the function, so you need to change this line...


Set @AverageSymbol = [ fnChangeSymbolToNumber] (@Symbol1) + [fnChangeSymbolToNumber](@Symbol2) + [fnChangeSymbolToNumber](@Symbol3)

To:

 Set @AverageSymbol = [!]dbo.[/!][fnChangeSymbolToNumber](@Symbol1) + [!]dbo.[/!][fnChangeSymbolToNumber](@Symbol2) + [!]dbo.[/!][fnChangeSymbolToNumber](@Symbol3)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[saintedmunds]

Hi
Thank you for your reply but unfortuantly ASCII codes dont start A = 1 B= 2 etc so A = 67 then when you divide it, it doesnt come out with the right Char.

Thanks again

 

[gmmastros]

Here's another version of the same thing.

Alter FUNCTION fnAverageSymbols
(
@Symbol1  varchar(1),
@Symbol2 varchar(1),
@Symbol3 varchar(1)
)
Returns VarChar(1)
As 
  Begin
    Return (Select Char(Avg(Ascii(Data)))
            From   (
                     Select Upper(@Symbol1) As Data
                     Union All Select Upper(@Symbol2) 
                     Union All Select Upper(@Symbol3)
                   ) As A
            )
  End
go
Select dbo.fnAverageSymbols('A','B','c')

And yes... the ASCII function does work properly.

Obviously, the average between A, B, And C is B

So

(A + B + C) / 3 = B

The ASCII value for A is 65. So...

(65 + 66 + 67) / 3
(198) / 3
66

The 'char' value of 66 is B, so it does work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[saintedmunds]

Hi

Thank you for your quick reply but when i do this

dbo.fnAverageSymbols('C', 'B', 'C') I want it to return C but its returning B

Any Ideas?

Cheers

 

[bborissov]

ROUND() the result.

CREATE FUNCTION "fnAverageSymbols"
(
@Symbol1  varchar(1),
@Symbol2 varchar(1),
@Symbol3 varchar(1)
)

RETURNS varchar(1)
AS
 BEGIN
      Declare @AverageSymbol numeric(11,7),
              @fnAverageSymbol varchar(1)

      Set @AverageSymbol = ASCII(UPPER(@Symbol1)) +
                           ASCII(UPPER(@Symbol2)) +
                           ASCII(UPPER(@Symbol3))

      set @AverageSymbol = @AverageSymbol / 3.0

      set @fnAverageSymbol  =  CHAR(ROUND(@AverageSymbol,0))

RETURN @fnAverageSymbol
END

again not tested
Thank you George for UPPER case. I completely forgot about it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

Rounding problems.

The ASCII value for B is 66. The Opposite of the ASCII function is CHAR, so...

[tt]
Select Char(66) -- B
Select Char(66.1) -- B
Select Char(66.6) -- B (it does not round)
Select Char(67) -- c
[/tt]

Since rounding the problem, we need to accommodate for that. Like this...

Alter FUNCTION fnAverageSymbols
(
@Symbol1  varchar(1),
@Symbol2 varchar(1),
@Symbol3 varchar(1)
)
Returns VarChar(1)
As 
  Begin
    Return (Select Char([!]Round([/!]Avg([!]1.0 *[/!] Ascii(Data))[!], 0)[/!])
            From   (
                     Select Upper(@Symbol1) As Data
                     Union All Select Upper(@Symbol2) 
                     Union All Select Upper(@Symbol3)
                   ) As A
            )
  End

Multiplying by 1.0 will cause a conversion to the float data type, allowing fractional numbers to be preserved. Then, rounding the number will cause values (like 66.6666) to be rounded to 67.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[saintedmunds]

Works great

Thanks

 

[saintedmunds]

Hi

One more thing it possible that if one of the symbols is Null that it can still work.

I have just tried it with a A B null and i get nothing also would need to devide by 2 not 3 and if there is only one symbol just to return that one.

Any ideas would be great

Thanks

 

[gmmastros]

Use my code instead of boris's. My function accomodates null inputs because it is using the AVG function (which accomodates nulls).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

[COLOR=blue]CREATE[/color] [COLOR=#FF00FF]FUNCTION[/color] "fnAverageSymbols"
(
@Symbol1 [COLOR=blue]varchar[/color](1) = NULL,
@Symbol2 [COLOR=blue]varchar[/color](1) = NULL,
@Symbol3 [COLOR=blue]varchar[/color](1) = NULL
)
RETURNS [COLOR=blue]varchar[/color](1)
[COLOR=blue]AS[/color]
 [COLOR=blue]BEGIN[/color]
      [COLOR=blue]Declare[/color] @AverageSymbol [COLOR=blue]numeric[/color](11,7),
              @fnAverageSymbol [COLOR=blue]varchar[/color](1),
              @DeleteTo [COLOR=blue]int[/color]

      [COLOR=blue]SET[/color] @DeleteTo = 3
      [COLOR=blue]IF[/color] @Symbol1 [COLOR=blue]IS[/color] NULL
         [COLOR=blue]BEGIN[/color]
            [COLOR=blue]SET[/color] @Symbol1= [COLOR=blue]CHAR[/color](0)
            [COLOR=blue]SET[/color] @DeleteTo = @DeleteTo - 1
         [COLOR=blue]END[/color]

      [COLOR=blue]IF[/color] @Symbol2 [COLOR=blue]IS[/color] NULL
         [COLOR=blue]BEGIN[/color]
            [COLOR=blue]SET[/color] @Symbol2= [COLOR=blue]CHAR[/color](0)
            [COLOR=blue]SET[/color] @DeleteTo = @DeleteTo - 1
         [COLOR=blue]END[/color]

      [COLOR=blue]IF[/color] @Symbol3 [COLOR=blue]IS[/color] NULL
         [COLOR=blue]BEGIN[/color]
            [COLOR=blue]SET[/color] @Symbol3= [COLOR=blue]CHAR[/color](0)
            [COLOR=blue]SET[/color] @DeleteTo = @DeleteTo - 1
         [COLOR=blue]END[/color]

     [COLOR=blue]IF[/color] @DeleteTo = 0
        [COLOR=blue]RETURN[/color] NULL

      [COLOR=blue]Set[/color] @AverageSymbol = [COLOR=#FF00FF]ASCII[/color]([COLOR=#FF00FF]UPPER[/color](@Symbol1)) +
                           [COLOR=#FF00FF]ASCII[/color]([COLOR=#FF00FF]UPPER[/color](@Symbol2)) +
                           [COLOR=#FF00FF]ASCII[/color]([COLOR=#FF00FF]UPPER[/color](@Symbol3))

      [COLOR=blue]set[/color] @AverageSymbol = (1.0*@AverageSymbol) / @DeleteTo

      [COLOR=blue]set[/color] @fnAverageSymbol  =  [COLOR=blue]CHAR[/color]([COLOR=#FF00FF]ROUND[/color](@AverageSymbol,0))

[COLOR=blue]RETURN[/color] @fnAverageSymbol
[COLOR=blue]END[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[kaht]

Any ideas would be great

Sounds like you're looking less for "ideas" and more for solutions dropped straight into your lap. Why don't you post what you've attempted to fix your null situation and then I'm sure one of the experts will help you modify your code. You keep changing the scope of your question to squeeze more solutions out each time. It's common practice to start a new thread when the question changes. And while you're at it, how about clicking the "thank **** for this valuable post" links for each person that helped you above to award them some votes for the tipmaster of the week?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

Thanks kaht!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[saintedmunds]

Thank you bborissov