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
Can anyone tell me a difficult situation who have handled while creating Datastage jobs?
Difference between ‘validated ok’ and ‘compiled’ in data stage?
What are the types of containers and how to create them?
Can you filter data in hashed file?
Does datastage support slowly changing dimensions ?
What can we do with datastage director?
How to find value from a column in a dataset?
Demonstrate experience in maintaining quality process standards?
if we using two sources having same meta data and how to check the data in two sources is same or not? and if the data is not same i want to abort the job ?how we can do this?
In Datastage, how you can fix the truncated data error?
What is merge stage?
hi.... am facing typical problem in every interview " I need some critical scenarios faced in real time" plz help me guys
What are the components of datastage?
Can you explain players in datastage?
tell me 5 situations when we r using oracle db stages like orecle connector, oracle enterprise