1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),

Categories >> Software >> Data Warehouse >> Informatica
Suggest New Category

1
2
3
*
4
5
6
*
7
8
9

how to load records between two stars(*),

Question Posted / prasad_dwh09

10 Answers
14399 Views
Cap Gemini, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / sunny

select substr(
(select substr('123*456*789', 1 ,
instr('123*456*789','*',1,2)-1) from dual)
,instr(
(select substr('123*456*789', 1 ,
instr('123*456*789','*',1,2)-1) from dual)
,'*',1)+1) from dual;

Is This Answer Correct ?

6 Yes

1 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / ankur

small change in the above post..
add an exp after source qualifier...with two more ports

v_out as variable =iif((is_number(inputport),v_out,v_out+1)
output=v_out

so output from this exp will be
1 0
2 0
3 0
* 1
4 1
5 1
6 1
* 2
7 2
8 2
8 2

now add a filter where output=1 and in_number(input)

Thanks

Is This Answer Correct ?

4 Yes

3 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / krishna

Mapping
source - SQ -> Exp T/r -> Filter -> target

Exp t/r:
create 4 variable and 3 output ports.
v_curr,v_flag,o_filter,o_flag,v_count,v_prev,0_count

v_flag = iif(v_currnt = '*' OR v_prev = '*' or flag_v=1,
1,0)
v_count = iif(v_prev='*',count+1,count)
o_filter = iif(No!= '*',1,0)
o_flag = v_flag
0_count = v_count

filter t/r:
condition: o_flag=1 AND o_filter=1 AND 0_count!=2

Is This Answer Correct ?

1 Yes

1 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / lakshmi

Use Replacechr() in Expression T/r after Source Qualifier
and replace '*' with Space

Then In Filter T/r Use Filter condition as below
iif(isspaces(field,0,1))

Is This Answer Correct ?

2 Yes

2 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / siva riddle

first of all , understand the requirement
it's.
records in between * 456 * ,
how to get this 3 records.

Is This Answer Correct ?

0 Yes

0 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / guest

u can use mapping variable in exp transformation.
and do
decode(v_curr='*' or v_prev='*',setvariable($$,$$+1),$$)
and last port in exp setvariable($$,0) and accrdingly filter the non required...

Is This Answer Correct ?

1 Yes

2 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / ankur

Is This Answer Correct ?

2 Yes

3 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / ankur saini

hey guys c if this works...

add an exp after source qualifier...with two more ports

v_out as variable =iif((is_number(inputport),0,v_out+1)
output=v_out

so output from this exp will be
1 0
2 0
3 0
* 1
....
* 2
...

now add a filter where output>0

Thanks

Is This Answer Correct ?

0 Yes

4 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / anas furquan

Use Filter transformation with below condition:
IIF(FIELD!='*',1,0)

Mapping:
Source-->SQ-->Filter-->Target

This works. :)

Is This Answer Correct ?

1 Yes

8 No

1 2 3 * 4 5 6 * 7 8 9 how to load records between two stars(*),..

Answer / chandu

By using filter transformation we can achieve this scenario.

Filter condition :column>=4 and column name<=6

Is This Answer Correct ?

4 Yes

14 No

Post New Answer