Interested to Buy Any Domain ? << Click Here >> for more details...
i have source data like
id name sal
1 a 65020
2 b 78250
3 c 58264
but how to get target like this
id name sal
1 a Sixty five thousand twenty
2 b Seventy thousand two fify
3 c Fifty eight thousand two sixty four
- 3 Answers
- 7345 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / rahul singh
We can use a simple function (TO_CHAR(TO_DATE(1234567,'J'),
'JSP')) this will return you ONE MILLION TWO HUNDRED
THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN . But this has
limitation to be used only for numbers between 1 and 5373484
.For numbers larger than that use following function and
call it in informatica through external function
transformation .
create or replace
2 function spell_number( p_number in number )
3 return varchar2
4 as
5 type myArray is table of varchar2(255);
6 l_str myArray := myArray( '',
7 ' thousand ', ' million ',
8 ' billion ', ' trillion ',
9 ' quadrillion ', '
quintillion ',
10 ' sextillion ', ' septillion ',
11 ' octillion ', ' nonillion ',
12 ' decillion ', ' undecillion ',
13 ' duodecillion ' );
14
15 l_num varchar2(50) default trunc( p_number );
16 l_return varchar2(4000);
17 begin
18 for i in 1 .. l_str.count
19 loop
20 exit when l_num is null;
21
22 if ( substr(l_num, length(l_num)-2, 3) <> 0 )
23 then
24 l_return := to_char(
25 to_date(
26 substr(l_num,
length(l_num)-2, 3),
27 'J' ),
28 'Jsp' ) || l_str(i) || l_return;
29 end if;
30 l_num := substr( l_num, 1, length(l_num)-3 );
31 end loop;
32
33 return l_return;
34 end;
| Is This Answer Correct ? | 4 Yes | 0 No |
Answer / chiky
if its in oracle database,use
select id, name, (SELECT TO_CHAR (TO_DATE sal, 'j'), 'jsp')
FROM DUAL) sal from <source_table_name>
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / krish
decode(sal,65020,Sixty five thousand twenty)
decode(sal,78250,Seventy thousand two fify)
decode(sal,58264,Fifty eight thousand two sixty four)
| Is This Answer Correct ? | 3 Yes | 10 No |
what is the method of error handling when session is running?
What are the hard ware required for a normal sized data warehouse? and What are the software required for a normal kind of data warehouse?
What is the difference between writing a joiner query in ANSI style and THETA style?
If I am having 6 flat files in data. How can you load the data at a time
WAT IS THE MEANING OF UPGRADTION OF REPOSITORY?
Workflow is long running due to long running sql query so when we refer the query plan it tells the issue is due to partition of the db table. How to handle this?
Can we use procedural logic inside infromatica? If yes how, if now how can we use external procedural logic in informatica?
we have to use order by,where,having we to implement sql query
How can you join two tables without any common column present in them?
Session S1, S2, and S3. In the session S3 I want to load every Saturday. How it is possible?
if i have one mapping and we already tuned that mapping for performance,everything is fine and loading will take 1 hr,so without doing any change in mapping how could we reduce the loading time from 1hr to 1/2 hr.
If you want to create indexes after the load process which transformation you choose?
Teradata 
