cancel
Showing results for 
Search instead for 
Did you mean: 

Not work well

former_member193355
Contributor
0 Kudos

Hi Experts,

Our partner have created function of SQL that will display amount in word in SAP B1. Here is the function:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER function [dbo].[amountinwords](@n bigint )

--Returns the number as words.

returns VARCHAR(255)

as

BEGIN

DECLARE @i int, @temp char(1), @s VARCHAR(20), @result VARCHAR(255)

SELECT @s=convert(varchar(20), @n)

SELECT @i=LEN(@s)

SELECT @result=''

WHILE (@i>0)

BEGIN

SELECT @temp=(SUBSTRING(@s,@i,1))

IF ((LEN(@s)-@i) % 3)=1

IF @temp='1'

SELECT @result=CASE (SUBSTRING(@s,@i+1,1))

WHEN '0' THEN 'Ten'

WHEN '1' THEN 'Eleven'

WHEN '2' THEN 'Twelve'

WHEN '3' THEN 'Thirteen'

WHEN '4' THEN 'Fourteen'

WHEN '5' THEN 'Fifteen'

WHEN '6' THEN 'Sixteen'

WHEN '7' THEN 'Seventeen'

WHEN '8' THEN 'Eighteen'

WHEN '9' THEN 'Nineteen'

END' 'CASE

WHEN ((LEN(@s)-@i)=4) THEN 'Thousand '

WHEN ((LEN(@s)-@i)=7) THEN 'Million '

WHEN ((LEN(@s)-@i)=10) THEN 'Billion '

WHEN ((LEN(@s)-@i)=13) THEN 'Trillion '

ELSE ''

END+@result

ELSE

BEGIN

SELECT @result=CASE (SUBSTRING(@s,@i+1,1))

WHEN '0' THEN ''

WHEN '1' THEN 'One'

WHEN '2' THEN 'Two'

WHEN '3' THEN 'Three'

WHEN '4' THEN 'Four'

WHEN '5' THEN 'Five'

WHEN '6' THEN 'Six'

WHEN '7' THEN 'Seven'

WHEN '8' THEN 'Eight'

WHEN '9' THEN 'Nine'

END' ' CASE

WHEN ((LEN(@s)-@i)=4) THEN 'Thousand '

WHEN ((LEN(@s)-@i)=7) THEN 'Million '

WHEN ((LEN(@s)-@i)=10) THEN 'Billion '

WHEN ((LEN(@s)-@i)=13) THEN 'Trillion '

ELSE ''

END+@result

SELECT @result=CASE @temp

WHEN '0' THEN ''

WHEN '1' THEN 'Ten'

WHEN '2' THEN 'Twenty'

WHEN '3' THEN 'Thirty'

WHEN '4' THEN 'Fourty'

WHEN '5' THEN 'Fifty'

WHEN '6' THEN 'Sixty'

WHEN '7' THEN 'Seventy'

WHEN '8' THEN 'Eighty'

WHEN '9' THEN 'Ninety'

END' '@result

END

IF (((LEN(@s)-@i) % 3)=2) OR (((LEN(@s)-@i) % 3)=0) AND (@i=1)

BEGIN

SELECT @result=CASE @temp

WHEN '0' THEN ''

WHEN '1' THEN 'One'

WHEN '2' THEN 'Two'

WHEN '3' THEN 'Three'

WHEN '4' THEN 'Four'

WHEN '5' THEN 'Five'

WHEN '6' THEN 'Six'

WHEN '7' THEN 'Seven'

WHEN '8' THEN 'Eight'

WHEN '9' THEN 'Nine'

END ' 'CASE

WHEN (@s='0') THEN 'Zero'

WHEN (@temp<>'0')AND( ((LEN(@s)-@i) % 3)=2) THEN 'Hundred '

ELSE ''

END + CASE

WHEN ((LEN(@s)-@i)=3) THEN 'Thousand '

WHEN ((LEN(@s)-@i)=6) THEN 'Million '

WHEN ((LEN(@s)-@i)=9) THEN 'Billion '

WHEN ((LEN(@s)-@i)=12) THEN 'Trillion '

ELSE ''

END+ @result

END

SELECT @i=@i-1

END

return REPLACE(@result,' ',' ')

END

when I excute it by using this following syntax :

select [dbo].[amountinwords](1000000), the result is one million thousand. What is the cause of this error ? I expected the result is one million only. Pls advice. I appreciate your help so much. TIA

Steve

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member193355
Contributor
0 Kudos

We replaced the function with new one. It solved now

Steve

Former Member
0 Kudos

Hi Steve,

Is it for PLD? If yes, you can use build in function in PLD. In the format tab, tick option "Sum in Words".

Cheers

former_member193355
Contributor
0 Kudos

Hi Yeni,

It can't solve this issue since the need of amount in word is not on the printing but also in the form or document

Steve

Former Member
0 Kudos

Hi Steve........

Run your script as Function into the SQL database and display it via FMS as I'm doing like that.....

Regards,

Rahul

former_member193355
Contributor
0 Kudos

Dear Experts,

I really beg your help to solve this issue. We can't ask the partner to do that because it is out of support and maintenance scope.

Pls help.

Seve