Input Data is:
Emp_Id, EmpInd
100, 0
100, 0
100, 0
101, 1
101, 1
102, 0
102, 0
102, 1
103, 1
103, 1
I want Output
100, 0
100, 0
100, 0
101, 1
101, 1
Means Indicator should either all ZEROs or all ONEs per
EmpId.
Impliment this using SQL and DataStage both.
Answer Posted / lb14447
The sql query would be
SELECT * FROM EMPTEST WHERE EMP_ID IN (SELECT EMP_ID FROM EMPTEST GROUP BY EMP_ID HAVING SUM(EMP_IND)/COUNT(EMP_IND) = 0
OR SUM(EMP_IND)/COUNT(EMP_IND) = 1);
Datastage implementation:
SRC --> CPY ----> AGG---> FILTER
- |
- |
- |
- |
- |
--------> Look up ----> TGT
In the Aggregator stage calculate the Sum and Count fields.In the filter stage bypass the unwanted records using Sum and Count calculated in Aggr stage.
Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
what are the devoleper roles in real time? plz tell i am new to datastage....
What are some different alternative commands associated with "dsjob"?
What are the some differences between 7.x and 8.x version of datastage?
How do you schedule or monitoring the job?
Hi guys, Please design a job for dis requirement with derivation(solution). my source table like dis. emp_no qualification 1 a 1 c 2 a 3 c 3 b To loaded to target like dis emp_no qualification 1 b 2 b 2 c 3 a my requirement is every employer have three qualifications i.e a,b and c. what qualification missed in source table that will be move to target systems. Hope u got it the requirement. Right Thanks.
What are the steps required to kill the job in Datastage?
Can you explain engine tier in information server?
What are routines in datastage?
How to perform incremental load in datastage?
Define Routines and their types?
options available in sequence job to run,validate?
What is the difference between the local container and shared container?
what is use of SDR function?
How you Implemented SCD Type 1 & Type 2 in your project?
How and where you used hash file?