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
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 |
Had any one faced informatica(ETL/Developer), Datawarehouseing interview in UK. Than plz help me (i have any exprience of 3yrs informatica,datawarehousing,oracle,teradata) 1.hw the procedure will b here 2. wht type of question's they will b asking. 3.In which area they concertate more. since this is the first time im facing interview in UK. plz help ASAP.it will b a great help for me thanks to All in Advance
I have source like col1,col2,col3,col4 and the values are like 3,6,1,7 1,5,3,8 2,1,5,6 i want the output like 3,6,7 5,3,8 2,5,6 How we will achieve in this scenario in informatica level. Please help me.. Thanks in advance..
how to get flatfile containg 10 records half records one target another half another target
what is left outer join?
i have flat file it contains 'n' number of records, i need to send half of the records to one target table and Remaining half to another target table. can any tell me procedure.
if i have source with 100 records target with 100 records and we lookup on another database table and it has 10 million record so what is the method of limiting that much record in lookup table?
How to create the source and target database connections in server manager?
How the informatica server sorts the string values in Rank transformation?
What do you mean by enterprise data warehousing?
Hi all , I am planning for informatica S - PowerCenter 8 Mapping Design certification. I have rewuired the dumps for the same. if anyone of you having the same pl. share it with me. This will be of great help. My contact is : sagardev7@gmail.com TIA , Sagar
Can a joiner be used in a mapplet.
If u select groupby port in aggregator what is output and dont select groupby option what is output