i have 1000 records in my dource table, the same i have in
target ,but a new column added in target as "batchno", and
this column adds no 10 for 1st 100 records and 20 for next
100 records and 30 next 100 records and vice versa. how to
acheive this?
Answers were Sorted based on User's Feedback
Answer / srini
SQ>>EXP>>>TAR
Generate seq in exp and check for condition like
iif(seq<100,10,iif(seq>100 and seq<=200,20,iif(seq>200 and seq<=300,30,40)))
Is This Answer Correct ? | 12 Yes | 3 No |
Answer / rocky!!
Here is another answer.
--> SEQ1
SD --> SQ --> EXP --> EXP --> TGT
--> SEQ2
1) Add SEQ1. Keep Starting value as 1 with cycle attribute set in after end value 100.
2) Add Another SEQ2. This is to keep a tab on the record count on the incoming data flow.
3) Get NEXTVAL & NEXTVAL1 from SEQ1 and SEQ2 and dump it in EXP.
4) in EXP, add Varibale port "V_OUTPUT_DECISION" and write code "CEIL(NEXTVAL1/100)"
5) in EXP, add variable "V_BATCH_NO" and initialise value as 10.
6) in EXP, add output port "OUTPUT_DECISION_BATCH_NO" and write code as "V_OUTPUT_DECISION*V_BATCH_NO"
7) connect fields viz, YEAR, DESC, MONTH, SALARY and BATCHNO to Target.
I guess this is the generic code. Bring any number records, should work.
Cheers!!
Is This Answer Correct ? | 3 Yes | 0 No |
Please follow the below step.
1.Create sequence geneteor which will start with 1.
2.Connect the nextval port to expression t/p.
3.connect port from sq transformation to expression t/p.
4.Create below port in the expression transformation.
a. NEXTVAL_OUT(Variable port)= NEXTVAL-1
b.rec_con(Variable port)= IIF(NEXTVAL_OUT % 100 = 0, REC_CON+10,REC_CON)
c. REC_CON_OUT(Out put port)= rec_con
5. If you have primary key on the target table then use update startegy t/r to update the targer.
6. If you don't have primary key on the target table then use update voerride in the properties tab of the target.
7. U can use the below query to update the target by update override.
UPDATE EMP_UPDATE SET REC_COUNT = :TU.REC_COUNT WHERE EMPID = :TU.EMPID
Please correct me if anything wrong.
Thanks and Regards
Santosh Kumar Sarangi
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / nitin
Source->SQ->Sequence->Exp->Target
In Sequence Generator:
Give Start Value = 1
End value = 100
Check the Properties as CYCLE.
Give the following condition In Expression Transformation:\
O_SEQUENCE= NEXT_VAL
COUNT = IIF(O_SEQUENCE > 1 AND O_SEQUENCE <= 100, COUNT+10)
Make the default value of COUNT as 0
Is This Answer Correct ? | 1 Yes | 6 No |
Differnce between filter and router?
I'd like the load to be triggered by client. By placement of a file or somehow. How is it done in Informatica? I'm using version 7.1.4 This is so urgent - I really appreciate your help :-)
3 Answers Alcatel-Lucent, ECI Telecom,
What are mapping parametres and variables in informatica?
select count(1) from emp? what is the o/p?
write a query to follwoing table amount year quarter 254556 2003 first 546332 2003 second 129034 2003 third 490223 2003 fourth 165768 2004 first 265443 2004 second 510412 2004 third 435690 2004 fourth i want the output year q1_amount q2_amount q3_amount q4_amount 2003 254556 546332 129034 490223 2004 165768 265443 510412 435690
CANNOT USE PARAMETER FILE! Hi all, I am trying to use parameter file for my workflow. This could help me to filter records where CITY = 'Portland' Following is what I have done: **in Designer - create new parameter : $$PARA_FIL, Parameter, String, IsExprVar=TRUE, Initial value = [empty] - Source Qualifier/ Properties/Source Filter: CUSTOMERS.CITY='$$PARA_FIL' **Create Parameter file: C:\Informatica\PowerCenter8.6.0 \server\infa_shared\BWParam\DynamicParamTest.txt $$PARA_FIL='Portland' **Configure workflow to use the parameter file: Edit Workflow/Properties/Parameter Filename: C:\Informatica\PowerCenter8.6.0 \server\infa_shared\BWParam\DynamicParamTest.txt I also configured directory of parameter file for session task. However, I just got this in the session log: [SQ_CUSTOMERS] SQL Query [SELECT CUSTOMERS.CUSTOMER_ID FROM CUSTOMERS WHERE CUSTOMERS.CITY='$$PARA_FIL'] No record has been loaded to target. It seems that the parameter file has not been read. I cannot understand the reason why. Could any of you kindly suggest me anything? Thanks
In a scenario I want to change the dimensions of a table and normalize the denomralized table which transformation can I use?
My source contains 10 records with 5 columns.What happens if I select group by all columns in Aggregator T/R?
source table A having 100000 records. and target B table having 100000 records. now iam updating B records using update query. i don't know which record is update in target. write a sql query get all updated records from target table B.
Hi experts, can anyone tell how much we use plsql in real time
What are the types of metadata that stores in repository?
how to remove staging area