Scenario:
For a give float number I need to find out decimal length and precision length
TEST (SOURCE)
N_FORMAT VARCHAR2(10)
TEST_TRG (TARGET)
N_FORMAT VARCHAR2(10)
DEC_LEN NUMBER(7)
FRACT_LEN NUMBER(3)
Assumptions:
1.Aware of creating model
2.Aware of creating Interface (table to table load)
Steps:
1.Go to Designer Navigator --> Go to Project Accordion --> Expand Project --> Right click on User Functions and select New User Function
2. Definition Tab
Name : DEC_LEN
Group : LENGTH
Syntax:
DEC_LEN($(P1))
[Note: Syntax : <function_name>(<parameter_list>)
3. Implementation Tab
Click on Plus Symbol. Provide following code
(CASE WHEN $(P1) IS NULL THEN 0
WHEN INSTR($(P1),'.') = 0 THEN LENGTH($(P1))
ELSE INSTR($(P1),'.')-1
END)
Select Technology as "oracle"
4. Similarly create one more function as mention below
Definition Tab
Name : FRACT_LEN
Group : LENGTH
Syntax:
FRACT_LEN($(P1))
5.Implementation Tab
(CASE WHEN $(P1) IS NULL THEN 0
WHEN INSTR($(P1),'.') = 0 THEN 0
ELSE LENGTH($(P1))-INSTR($(P1),'.')
END)
Select Technology as "oracle"
6.In the interface mapping should be as below:
Output:
For a give float number I need to find out decimal length and precision length
TEST (SOURCE)
N_FORMAT VARCHAR2(10)
TEST_TRG (TARGET)
N_FORMAT VARCHAR2(10)
DEC_LEN NUMBER(7)
FRACT_LEN NUMBER(3)
Assumptions:
1.Aware of creating model
2.Aware of creating Interface (table to table load)
Steps:
1.Go to Designer Navigator --> Go to Project Accordion --> Expand Project --> Right click on User Functions and select New User Function
2. Definition Tab
Name : DEC_LEN
Group : LENGTH
Syntax:
DEC_LEN($(P1))
[Note: Syntax : <function_name>(<parameter_list>)
3. Implementation Tab
Click on Plus Symbol. Provide following code
(CASE WHEN $(P1) IS NULL THEN 0
WHEN INSTR($(P1),'.') = 0 THEN LENGTH($(P1))
ELSE INSTR($(P1),'.')-1
END)
Select Technology as "oracle"
4. Similarly create one more function as mention below
Definition Tab
Name : FRACT_LEN
Group : LENGTH
Syntax:
FRACT_LEN($(P1))
5.Implementation Tab
(CASE WHEN $(P1) IS NULL THEN 0
WHEN INSTR($(P1),'.') = 0 THEN 0
ELSE LENGTH($(P1))-INSTR($(P1),'.')
END)
Select Technology as "oracle"
6.In the interface mapping should be as below:
Output:
TARGET N_FORMA IS NOT NUMBER(10,3) IT IS VARCHAR2(10) PLEASE IGNORE DATATYPE IN THE MAPPING SCREEN SHOT
ReplyDelete