|
|
|
|
|
|
Re: show increasing digit 1 to 100 in a text field after pressing one by one [message #595944 is a reply to message #595941] |
Mon, 16 September 2013 07:43   |
cookiemonster
Messages: 13966 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
medisys wrote on Mon, 16 September 2013 13:15thanks littlefoot
its working but i like to add that when number is 100 then if press button then it will start from 1 again so can u please help me for that?
thanks
So you need an if statement that checks the current value and if it's 100 sets it to 1 instead of incrementing it.
You really should be able to write that without any help.
|
|
|
Re: show increasing digit 1 to 100 in a text field after pressing one by one [message #595990 is a reply to message #595944] |
Tue, 17 September 2013 01:03   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Alternatively, DECODE (or CASE) could do the same job. Have a look at the following SQL*Plus example:SQL> select nvl(decode(&text_item, 100, 0, &text_item), 0) + 1 from dual;
Enter value for text_item: 99
Enter value for text_item: 99
NVL(DECODE(99,100,0,99),0)+1
----------------------------
100
SQL> /
Enter value for text_item: 100
Enter value for text_item: 100
NVL(DECODE(100,100,0,100),0)+1
------------------------------
1
SQL> /
Enter value for text_item: null
Enter value for text_item: null
NVL(DECODE(NULL,100,0,NULL),0)+1
--------------------------------
1
SQL> /
Enter value for text_item: 25
Enter value for text_item: 25
NVL(DECODE(25,100,0,25),0)+1
----------------------------
26
SQL>
|
|
|
|
|
|
|
|
|
|
|
|
Re: show increasing digit 1 to 100 in a text field after pressing one by one [message #596127 is a reply to message #596123] |
Wed, 18 September 2013 03:37   |
 |
medisys
Messages: 18 Registered: March 2013 Location: Riyadh
|
Junior Member |
|
|
i created function spell_number........function spell_number( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-2, 3) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
return l_return;
end;
and in when validate trigger i write the code ........ :spell:=spell_number(:nmbr);
|
|
|
|
Re: show increasing digit 1 to 100 in a text field after pressing one by one [message #596141 is a reply to message #596117] |
Wed, 18 September 2013 05:10   |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
medisys wrote on Wed, 18 September 2013 13:17thanks a lot latin kumar & littlefoot brother but another problem is if i want to spell number like 150.20( should be like (one hundred fifty and twenty)) or if i want to spell 00.21 ( should be ( twenty one cent) its not working... please help
latin? It's Lalit
I have an example code, which you can tweak using the previous "spell the numbers" code to get your task done -
SQL> WITH DATA AS
2 (SELECT '150.20' A FROM DUAL),
3 T(A,
4 B,
5 C) AS
6 (SELECT A, TO_CHAR(TO_DATE(SUBSTR(A, 1, 1), 'J'), 'JSP') B, 1 AS C
7 FROM DATA
8 UNION ALL
9 SELECT A,
10 CASE
11 WHEN SUBSTR(A, C + 1, 1) = '.' THEN
12 'DOLLARS AND'
13 WHEN SUBSTR(A, C + 1, 1) = '0' THEN
14 'ZERO'
15 ELSE
16 TO_CHAR(TO_DATE(SUBSTR(A, C + 1, 1), 'J'), 'JSP')
17 END B,
18 C + 1 AS C
19 FROM T
20 WHERE C < LENGTH(A))
21 SELECT LISTAGG (B, ' ') WITHIN GROUP (ORDER BY C) ||' '|| 'CENTS' AS "SPELLED" FROM T
22 /
SPELLED
--------------------------------------------------------------------------------
ONE FIVE ZERO DOLLARS AND TWO ZERO CENTS
Regards,
Lalit
|
|
|
Re: show increasing digit 1 to 100 in a text field after pressing one by one [message #596214 is a reply to message #596141] |
Thu, 19 September 2013 01:02   |
 |
medisys
Messages: 18 Registered: March 2013 Location: Riyadh
|
Junior Member |
|
|
cookiemonster brother could you please add necessary code for spell the number after decimal in my function
function spell_number( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-2, 3) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
return l_return;
end;
|
|
|
|
|
|
Re: show increasing digit 1 to 100 in a text field after pressing one by one [message #596222 is a reply to message #596217] |
Thu, 19 September 2013 02:37   |
cookiemonster
Messages: 13966 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I've already supplied the code to get the decimal bit.
Myself and Littlefoot and have described what you need to do with it.
You really need to try it yourself, it's not difficult since you already have example code to base it on.
If we supply all the code you'll never learn how it works and you really need to.
If you get stuck post what you tried and we will help, but we're not doing it all for you.
|
|
|
|
|